Return all rows from table one even if table two is empty or partial results found

Question

I am trying to return all the results from table one, AKA ship_skill_tree, while matching up the rows found in table two, AKA character_sheet_skills, even if the rows do not exist in table two.

SELECT c.`level` , t.`skillLevel` AS levelNeeded, i.`typeName`
FROM  `ship_skill_tree` t
LEFT JOIN  `character_sheet_skills` c ON t.`skillTypeID` = c.`typeID`
LEFT JOIN `invTypes` i ON i.`typeID` = t.`skillTypeID`
WHERE t.`shipTypeID` = 11176 AND c.`character_id` = 1;

Table One Data:

|shipTypeID|shipGroupID|skillTypeID|skillLevel
______________________________________________
|11011|26|3332|1
|11129|31|3327|1
|11132|31|3327|1
|11134|31|3327|1
|11172|830|3328|5
|11172|830|12093|1
|11174|893|3328|5
|11174|893|28615|1
|11176|831|3330|5
|11176|831|12092|1

Table Two Data:

|character_id|typeID|skillpoints|level|published
______________________________________________
|1|3300|1415|2|1
|1|3301|8000|3|1
|1|3327|256000|5|1
|1|3330|2829|2|1
|1|3340|181020|4|1
|1|3341|1024000|5|1
|1|3342|32000|3|1
|1|3343|32202|3|1
|1|3380|256000|5|1
|1|3385|256000|5|1
|1|3386|256000|5|1
|1|3392|256000|5|1
|1|3394|90514|4|1
|1|3402|256000|5|1
|1|3410|768000|5|1
|1|3411|135765|4|1
|1|3412|750|1|1
|1|3413|256000|5|1
|1|3416|45255|4|1
|1|3417|0|0|1
|1|3418|0|0|1
|1|3419|135765|4|1
|1|3420|181020|4|1
|1|3423|0|0|1
|1|3425|90510|4|1
|1|3426|45255|4|1
|1|3428|500|1|1
|1|3429|8000|3|1
|1|3436|45255|4|1
|1|3437|45255|4|1
|1|3438|500|1|1
|1|3449|256000|5|1
|1|3453|0|0|1
|1|3455|256000|5|1
|1|3456|226275|4|1
|1|11579|271530|4|1
|1|12186|0|0|1
|1|12187|0|0|1
|1|12188|0|0|1
|1|12190|22547|3|1
|1|12191|45255|4|1
|1|12192|45255|4|1
|1|12193|45255|4|1
|1|12195|45255|4|1
|1|16281|256000|5|1
|1|17940|1024000|5|1
|1|20342|1280000|5|1
|1|22551|40000|3|1
|1|22578|181020|4|1
|1|25739|0|0|1
|1|26252|16000|3|1
|1|26253|750|1|1
|1|26261|750|1|1
|1|32918|16000|3|1

invTypes table:

|typeID|typeName
________________
|3327|Spaceship Command
|3328|Gallente Frigate
|3330|Caldari Frigate
|3332|Gallente Cruiser
|12092|Interceptors
|12093|Covert Ops
|28615|Electronic Attack Ships

In the above query shipTypeID will always, or should always, be valid and match a record in table one, however, in table two, the rows that match may not exist. What I need is to output as follows:

|level|levelNeeded|typeName
___________________________
|2|5|Caldari Frigate
|NULL|1|Interceptors

Currently this is what is returned:

|level|levelNeeded|typeName
___________________________
|2|5|Caldari Frigate

EDIT: Solution!

SELECT c.`level` , t.`skillLevel` AS levelNeeded, i.`typeName`
FROM  `ship_skill_tree` t
LEFT JOIN  `character_sheet_skills` c ON t.`skillTypeID` = c.`typeID` AND c.`character_id` = 1
INNER JOIN `invTypes` i ON i.`typeID` = t.`skillTypeID` 
WHERE t.`shipTypeID` = 11176

JOIN conditions should be put in the `ON` clause, not the `WHERE` clause. — Barmar, Mar 31 '14 at 05:39
@Barmar, that was a alternate SQL, it was originally on the WHERE, I switched it to test around, I will correct in the original post. — 0mni, Mar 31 '14 at 06:17
SELECT c.`level` , t.`skillLevel` AS levelNeeded FROM `ship_skill_tree` t LEFT JOIN `character_sheet_skills` c ON t.`skillTypeID` = c.`typeID` WHERE t.`shipTypeID` = 11176 AND c.`character_id` = 1; — 0mni, Mar 31 '14 at 06:31

Barmar · Accepted Answer · 2014-04-01T14:19:51.213

1

You need to put any restrictions on the table being joined in the ON clause. If you put them in the WHERE clause it doesn't work, because the rows that don't have any matches will produce NULL for those columns, and the WHERE clause will filter them out.

SELECT c.`level` , t.`skillLevel` AS levelNeeded, i.`typeName`
FROM  `ship_skill_tree` t
LEFT JOIN  `character_sheet_skills` c ON t.`skillTypeID` = c.`typeID` AND c.`character_id` = 1
LEFT JOIN `invTypes` i ON i.`typeID` = t.`skillTypeID`
WHERE t.`shipTypeID` = 11176

DEMO

edited Apr 01 '14 at 14:19

answered Mar 31 '14 at 21:42

Barmar

741,623
53
500
612

In your demo, you have an `IS NOT NULL` check for the second table. If you're going to filter out null rows, you can just use an INNER JOIN instead of LEFT JOIN. See [DEMO](http://www.sqlfiddle.com/#!2/9c717f/4) – Barmar Apr 01 '14 at 14:18

score 0 · Answer 2 · answered Mar 31 '14 at 05:33

0

You need to use a right join or an outer join rather than a left join. Have a look through the Visual Representation of SQL Joins for a good overview

answered Mar 31 '14 at 05:33

botheredbybees

582
6
14

This is backwards. LEFT JOIN returns rows from table 1 with no match in table 2. RIGHT JOIN returns rows from table 2 with no match in table 1. – Barmar Mar 31 '14 at 05:36

Return all rows from table one even if table two is empty or partial results found

2 Answers2