mysql join returns no result when one side has no match

Question

I'm trying to get the names of components and their statuses from related tables, but when a component has no status, I don't get the name. My data:

table a
-------
pkey
component_name


table b
-------
pkey
foreign_key
status
userid

My query:

SELECT a.component_name, b.status FROM a LEFT JOIN b ON a.pkey=b.foreign_key WHERE a.pkey=1 AND b.userid=5

I will have multiple values for a.pkey, so ideally, I'd like to do something like this to get all the results at once:

SELECT a.component_name, b.status FROM a LEFT JOIN b ON a.pkey=b.foreign_key WHERE a.pkey IN (1,2,3) AND b.userid=5

Running these, I only get results when a record matches in both tables, but I'd like to get the component_name even if there is no status. How do I do this?

Thank you!

All right...some sample data:

table a
-------
1,course 1
2,course 2
3,course 3
4,course 4

table b
---------
1, 1, completed, 11
2, 2, completed, 11
3,1, cancelled, 10
4,4,completed,11

Expected results with user id 11:

course 1,completed
course 2, completed
course 3, NULL
course 4, completed

@killstreet "I'd like to get the component_name even if there is no status" your suggestion somewhat defeats that objective... — ADyson, Aug 03 '18 at 14:19
Also, mind checking the following url: https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server — killstreet, Aug 03 '18 at 14:20
Grimey can you give us some sample data and expected output, please. — ADyson, Aug 03 '18 at 14:20

score 1 · Accepted Answer · answered Aug 03 '18 at 14:25

1

you should not use left joined involved tables column in where condition move these conditions in on clause for the related tables

  SELECT a.component_name, b.status 
  FROM a 
  LEFT JOIN b ON a.pkey=b.foreign_key  AND b.userid=5 
  WHERE a.pkey=1


  SELECT a.component_name, b.status 
  FROM a 
  LEFT JOIN b ON a.pkey=b.foreign_key AND b.userid=5 
  WHERE a.pkey IN (1,2,3)

answered Aug 03 '18 at 14:25

ScaisEdge

131,976
10
91
107

That makes perfect sense. Thank you! – Grimey Aug 03 '18 at 14:55

mysql join returns no result when one side has no match

1 Answers1