Left Join acting like Inner Join

This is really embarrassing that I can’t figure this query out!! :confused:

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.

Table Layout:

INTEREST
---------
- id
- name
- category
MEMBER_INTEREST
----------------
- id
- member_id
- interest_id
- checked_on
- unchecked_on
MEMBER
----------------
- id
- name
and so on...

Sample Data

id    name        category
1    Football    Sports
2    Basketball    Sports
3    Baseball    Sports
4    Hunting        Outdoors
5    Fishing        Outdoors
6    Camping        Outdoors
7    Hiking        Outdoors
8    Knitting    Indoors
9    Sewing        Indoors
10    Cooking        Indoors
id    member_id    interest_id    checked_on    unchecked_on
1    19            3        2015-03-21
2    19            6        2015-03-21
3    19            8        2015-03-21

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

Results:

id    member_id    interest_id    checked_on    unchecked_on
1    19        3        2015-03-21    
2    19        6        2015-03-21    
3    19        8        2015-03-21    

What am I doing wrong?? :confused:

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 was changing things around and maybe posted the wrong data. Try this…

id    name        category    checked_on     unchecked_on     
3    Baseball    Sports        2015-03-21    NULL
6    Camping        Outdoors    2015-03-21    2015-03-21
6    Camping        Outdoors    2015-03-21    NULL
8    Knitting    Indoors        2015-03-21    NULL

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

move the WHERE condition into the ON clause

I figured this out last night looking at some old code.

It sure doesn’t make logical sense to me.

Why did the creators of SQL change things for Outer Joins?

could you please explain what you mean by “change things”

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.

“it seems inconsistent” – only to you, my friend

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.