Getting unique, but different rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Getting unique, but different rows

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

(untested)

Code: Select all

SELECT name, phone
FROM TABLE
GROUP BY name, phone
HAVING COUNT(phone) = 1;
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
Post Reply