Page 1 of 1

Getting unique, but different rows

Posted: Wed Oct 25, 2006 6:24 am
by someberry
Ok, this really has be boggled. I am trying to return rows which have unique characteristics, but whose row is not wholely unique.

For example, this is the database:

Code: Select all

id     | name      | phone
---------------------------
1        John        12345
2        Bob         23456
3        John        45678
4        Bob         45678
5        Bob         45678
6        Bill        56789
7        Bob         56789
In this example the query should pick out ID's #3, 4, 6, 7. This is because they have the same phone, but different names.

One name should have one or more unique phones. Two or more names should not have the same phone.

So, the phone should be unique to the name basically.

Anyone have any ideas about how to implement this? I am completely stuck on even where to begin :/

Thanks,
someberry

Posted: Wed Oct 25, 2006 6:34 am
by timvw
(untested)

Code: Select all

SELECT name, phone
FROM TABLE
GROUP BY name, phone
HAVING COUNT(phone) = 1;

Posted: Wed Oct 25, 2006 6:49 am
by someberry
Thanks timw, it worked, kind of.

Your query is returning the rows for each user with all their unique phone numbers.

However, it isn't taking into account the other users in the table. I would like to get rid of all the returned rows whose phone numbers are unique to them.

This would leave only the users which have phone numbers diplicated over a number of users.

I hope you get what I mean :)
Thanks,
someberry

Posted: Wed Oct 25, 2006 9:10 am
by timvw
your example table doesn't show how the numbers are related to the users... so i'm pretty sure, now that you have the basic idea of group by - having you can expand this query to take into consideration the other requirements...