Query help

[sidsel]

I have a table looking like this:

ID | email | other
14 |[e-mail] |other
15 |[e-mail] |other
16 |[e-mail] |other
17 |[e-mail] |other
18 |[e-mail] |other
19 |[e-mail] |other

How do I format my query to find all rows with the same email if the only variable I have is the ID?

Ie. if I know ID 14, all three rows with email "[e-mail]" should be returned.

[laserlight]

One possibility would be to use a subquery:

SELECT * FROM tablename WHERE email IN (SELECT email FROM tablename WHERE ID=x);

[sidsel]

I am using mysql 4.0 so this subquery does not work for me.

[liquorvicar]

How about joining the table to itself?

SELECT t2.* FROM tablename t1 left join tablename t2 USING (email) WHERE t1.id=x

[Sxooter]

that's close, but you want to exclude joining the same row to itself. and you probably don't want the same row pair twice either.,

select t2.* from tablename t1 join table tablename t2 on (t1.email=t2.email AND t1.id>t2.id)

[liquorvicar]

Good spot. I did think my solution looked a bit too clean.

[Sxooter]

Let's just say I've spent a lot of time fixing messed up data... Not my own, mind you.

🆒

[sidsel]

When I run the query below some rows come out multiple times. Can it be the 'id' that cause it? The 'id is set to be the primary key. Table contains "holes" as some entries are deleted.

select t2.* from mytable t1 join mytable t2 on (t1.email=t2.email AND t1.id>t2.id) where t1.email = '[e-mail]'

[Roger_Ramjet]

Add DISTINCT

[Sxooter]

You might have multiple dupes for some rows, in which case you'd get multiple matches.

Note that this kind of query is generally used to find the candidates to delete. the ones left behind should be non-dupes.

backup first of course.

You can also use a "select distinct t2.* from..." to get rid of the dups. But you likely need the dupes if you're going to use this to delete rows with.

BUT BACKUP FIRST... I'm not responsible for lost data here...

[sidsel]

Nope... no dupes in my table

If I run this: SELECT id FROM mytable WHERE email = '[e-mail]'
I get 5 unique rows with id: 3494, 4007, 4113, 4786, 4798

The query in my last post get me the following rows with id: 3494, 3494, 3494, 3494, 4007, 4007, 4007, 4113, 4113, 4786.

First row comes out 4 times, second row 3 times, third row 2 times... etc.

[tomhath]

select t2.* from tablename t1 join table tablename t2 on (t1.email=t2.email AND t1.id>t2.id)

Shouldn't this be selecting t1. instead of t2. ???

[Sxooter]

Coo, then adding a distinct should allow you to list JUST the dups, and keep one original. If you're looking to delete dups this should work, since 4798 didn't show up in the list.