Mysql - Join matches and non-matches

Question

This is related to my other question: http://stackoverflow.com/questions/2579249/managing-foreign-keys

I am trying to join the table of matches and non-matches.

So I have a list of interests, a list of users, and a list of user interests.

I want the query to return all interests, whether the user has the interest or not (should be null in that case), only where the user = x. Every time I get the query working its only matching interests that the user specifically has, instead of all interests whether they have it or not.

Answer 1

You should rather use LEFT JOINS

Something like

SELECT  *
FROM    interests i LEFT JOIN
        userinterests ui ON i.interestID = ui.interestID LEFT JOIN
        users u ON ui.userID = u.uiserID
WHERE userID = ? 

where is the user id you are looking for.

Answer 2

SELECT  * 
FROM    interests i 
LEFT JOIN  userinterests ui ON i.interestID = ui.interestID 
LEFT JOIN  users u ON ui.userID = u.uiserID 
and u.userID = ?  

If you put a where condition on a table that should have no records inteh main tbale, you convert the join from a left join to an inner join. The only time you should ever have a condition inthe where clasue for something one the right side of a left join is when you are searching for records that don't match (where u.userid is null, for instance)

Of course you should define the fields to be selected and never use select * in production code especially not when you have a join as it sends repeated information across the network (the data inteh join feilds is repeated) and is a waste of resources and poor prgramming practice for multiple reasons.