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 inter开发者_运维知识库ests 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

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

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 

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.

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