CODEWITHSUNDEEP
mysqljoin
Erik
Erik

Reputation: 227

MYSQL : Problem in writing where clause according to scenario

I have a DB (user_interests) set up with 3 fields: i_id (unique), interest_id, uid.

Then a second DB (interests) set up with the interests: interest_id (unique), interest_name

I'd like to do an SQL query to return a list of interests that two users have in common: User A (owner of a profile) and user B (you/viewer of a profile). I guess I need to query user_interests, then JOIN interests to get the name of the interest.

SELECT user_interests.i_id, user_interests.uid, interests.interest_name 
FROM databases.user_interests 
LEFT JOIN databases.interests 
ON interest.interest_id = user_interest.interest_id 
WHERE _______________

I'm confused about the where clause (if that is the correct way to do it at all). My goal is to get the interest_id from user_interests.interests where user_interests.uid is both A and then B (in separate rows).

I saw this link, but couldn't figure out what exactly I was missing: Group by x where y = A and B and C

Upvotes: 1

Views: 61

Answers (4)

Stuti
Stuti

Reputation: 1638

Your query is correct remove the where part and run it. It will give you the same output as you need...

Upvotes: 0

goggin13
goggin13

Reputation: 7986

I would solve it by joining two copies of user_interests, one which is filtered for user A (the profile owner), and one for user B, (the profile viewer). 

SELECT *
FROM interests I
INNER JOIN user_interests A ON
  A.interest_id = I.interest_id
  AND A.user_id = {profile owner}
INNER JOIN user_interests B ON
  B.interest_id = I.interest_id
  AND B.user_id = {profile viewer}

Alternatively, more along the lines of the snippet you provided, you could complete the where clause with something like...

SELECT * FROM interests
WHERE interest_id in (SELECT interest_id 
                      FROM users 
                      WHERE user_id = A)
      AND 
      interest_id in (SELECT interest_id 
                      FROM user_interests 
                      WHERE user_id = B)

Hope one of those works for you! Let me know if I can clarify

Upvotes: 1

Bryan
Bryan

Reputation: 6752

You may also create a where statement such as the following if you are looking to get a specific result set. I'm not discrediting the answer previously submitted, I am simply trying to help you with the specific WHERE statement you're looking for.

SELECT user_interests.i_id, user_interests.uid, interests.interest_name 
FROM databases.user_interests 
LEFT JOIN databases.interests 
ON interests.interest_id = user_interests.interest_id 
WHERE user_interests.uid IN ('A','B');

Please also note, that I changed your ON join to use interests and user_interests, with 's' appended to both, as those are the names of the table. They maintain the same schema name as they are assigned in the database.

Upvotes: 0

Ibu
Ibu

Reputation: 43840

I don't think you need the where clause in this case just remove it and you will get the set of data you are looking for:

SELECT user_interests.i_id, user_interests.uid, interests.interest_name 
FROM databases.user_interests 
LEFT JOIN databases.interests 
ON interest.interest_id = user_interest.interest_id

Upvotes: 0

Related Questions