3

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?

Improve this question
3
  • 1
    What do you mean the third column contains item s? Is it some sort of delimited list? If so this will be tricky unless you normalise your tables. Commented Apr 21, 2011 at 22:37
  • Is the items column a foreign key to a list of items? Can there exist two rows for the same username and item? Commented Apr 21, 2011 at 22:43
  • Hey guys, no, one user may own one item once. Both rows are foreign keys joining two tables. Commented Apr 21, 2011 at 22:50

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi Martin, is there any chance you could explain that a little for me? What exactly does t1 and t2 signifiy?
No I haven't taken these into account as that is the first I've heard of them! Am I to assume something like username (userid, name) and items (itemid, description) and useritemrelationship (userid,itemid)
Yes precisely. Sorry for my bad descriptive ability's :(, you have no idea how much you are helping me out right now. I am currently working on the 4 extra reputation I need to upvote you. Lol.
Wow Martin. You know your stuff. Same question though, what exactly do i and u signify? Are these simply variable names that work within SQL?
@Ferdia - They are just table alias names to save some typing. You put them after the table names in the FROM or JOIN and then use the short names in the other parts of the query.
|
0

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.

Improve this answer

1 Comment

Well how I have it set up is I have a table that describes the items, including name, and the item ID is transfered into the table I described above using a foreign key. The table I am searching is for each instance of ownership of an item in the other table. So this table exists to define the relationship between the users table, and the items table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.