CODEWITHSUNDEEP
phpmysql
Ferdia O'Brien
Ferdia O'Brien

Reputation: 869

Selecting duplicate values based on distinct values in another column

Ok, so lets say I have a table comprising three columns. The first column is a unique id, the second is usernames, and the third is items users own.

What I would like to do is select values from the items row, that two distinct usernames posses. How might I go about doing that? So for hypothetical sake lets say I want to see what items both bob and ruth possess.

Any help would be greatly appreciated.

Is there a way i can post tables to give an example of what I'm talking about?

Upvotes: 3

Views: 247

Answers (2)

Martin Smith
Martin Smith

Reputation: 452967

Based on what I think your table structure is...

SELECT i.itemid,
       i.description
FROM   items i
       JOIN useritemrelationship r
         ON i.itemid = r.itemid
       JOIN users u
         ON u.userid = r.userid
WHERE  u.name in ( 'Ruth', 'Bob' )
GROUP  BY i.itemid
HAVING COUNT(DISTINCT r.userid) = 2

Upvotes: 2

ftw
ftw

Reputation: 63

I might suggest creating a new table for the items with at least three fields: itemID (if the item names aren't unique), itemName, and ownerID. Then query from the new table:

SELECT itemName FROM items WHERE ownerID='ruths id' OR ownerID='bobs ID'

This would also help implement normalization which is generally a clean/organized etiquette to follow when establishing a database.

Upvotes: 0

Related Questions