This is really embarrassing that I can’t figure this query out!!
I am trying to build a query that shows every INTEREST, and denotes those that the current user has chosen.
For example, in my demo database there are 10 Interests that a person can choose from. I need to show all of those in the form - with a checkbox next to each. Then for member_id=19 (i.e. Karen), I want to show that she checked 3 Interests.
I figured this was a basic outer-join, but my query isn’t working.
When I run this Outer Join, instead of getting all of the Interests and the ones that Karen chose, I just get the ones Karen chose…
Query:
SELECT i.id, i.name, i.category, mi.checked_on, mi.unchecked_on
FROM interest AS i
LEFT JOIN member_interest AS mi
ON i.id = mi.interest_id
WHERE mi.member_id=19
ORDER BY i.category_sort ASC, i.interest_sort ASC
It looks like you’re only outputting results from the ‘member_interest’ table. I can’t see anything that matches the results with the full list of options from the ‘interest’ table. My SQL is a little rusty, so I can’t offer much in the way of reworking it, but I’m pretty sure that’s what I’m seeing
Is there any reason why the field names in your results are identical to your “members_interests” field names, rather than what your query looks to be selecting? There’s no ‘i.name’ in the results for example.
I think it’s the WHERE statement that’s doing it - it’s limiting the results to matched fields only.
EDIT: Definitely the WHERE - if you amend the example under ‘Try it yourself’ here - http://www.w3schools.com/sql/sql_join_left.asp - you’ll see you get the same effect. So in that sense, your query looks to be doing exactly what it should be doing. Next to work out how to re-frame it so you get what you expect.
[quote=“chrisofarabia, post:6, topic:116238, full:true”]
I think it’s the WHERE statement that’s doing it - it’s limiting the results to matched fields only.[/quote]
this is the answer
It seems inconsistent to restrict records in the WHERE clause for normal SELECTs and JOINS, but to move/change that logic to the ON clause for OUTER JOINS.
the ON condition is used to determine which rows should be joined, and the “outerness” of the join determines whether unmatched rows should be discarded
the WHERE clause then operates on whatever rows have survived the join
the creators of sql didn’t “change things” – they implemented two different mechanisms, and you shouldn’t confuse those mechanisms