Correct Syntax for MySQL Left join

Question

I'm new to inner join and left join , Hope somebody can help me with this ..

I have 3 tables ..

Shirts
Colors
Prices

Shirts:

sid | name
------------
01 | Sample1
02 | Sample2

Colors

sid | color | color_id
------------
01 | red | 900
02 | green | 090

Prices:

sid | price
------------
01 | 100
02 | 100

My query is:

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid`
WHERE `Shirts`.`sid`='02'

What I would like to achieve is:

sid | name | color | price
----------------------
02 | Sample2 | green | 100

What I get is:

sid | name | color | price
----------------------
null | null | null | null

I know there must be wrong with my query .. So can anyone please tell me what is the right query for that ?

Can problem be in column `GENDB_ID`? Must there be column Prices.SID? — veljasije, Aug 20 '13 at 06:30
your query is working just fine. I'm getting the proper results,same as you have expected. — Arham, Aug 20 '13 at 06:54

score 1 · Answer 1 · answered Aug 20 '13 at 06:31

1

I think you have to use:

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid`
WHERE `Shirts`.`sid`='02'

Prices.GENDB_ID is not a valid field or is not what you have to use (I suppose).

answered Aug 20 '13 at 06:31

Tobia Zambon

7,479
3
37
69

What is the type of sid? Are they all integers or texts or other? – Tobia Zambon Aug 20 '13 at 06:50

score 1 · Answer 2 · answered Aug 20 '13 at 06:32

1

Try the following. This part

`on `Shirts`.`sid` = `Prices`.`GENDB_ID

looks incorrect. Try instead:

on `Shirts`.`sid` = `Prices`.`sid`

answered Aug 20 '13 at 06:32

Conrad Lotz

8,200
3
23
27

Rahul Tripathi · Answer 3 · 2013-08-20T06:51:42.003

1

Try this:-

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid` AND  `Prices`.`price`= 100   //Change this part.
WHERE `Shirts`.`sid`='02'

edited Aug 20 '13 at 06:51

answered Aug 20 '13 at 06:32

Rahul Tripathi

168,305
31
280
331

score 1 · Answer 4 · answered Aug 20 '13 at 06:37

1

SELECT 
    `Shirts`.*, 
    `Colors`.`color`,
    `Prices`.`price` 
FROM 
    `Shirts` INNER JOIN `Colors` on `Shirts`.`sid` = `Colors`.`sid`
     JOIN `Prices` on `Shirts`.`sid` = `Prices`.`sid` 
WHERE `Shirts`.`sid`='02'

answered Aug 20 '13 at 06:37

Nanhe Kumar

15,498
5
79
71

score 0 · Answer 5 · edited May 23 '17 at 12:08

This should work:

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid`
WHERE `Shirts`.`sid`='02'

The reason you are getting an all null row is the GENDB_ID you are including in your join.

The different between INNER JOIN and LEFT JOIN is that an INNER JOIN will exclude any join combination between the two tables which doesn't result in a match while LEFT JOIN will force a row even if there is no match.

An example:

Shirts

sid  |   name 
------------ 
01  | Sample1 
02  | Sample2

Colors

sid |  color | color_id
------------ 
01 |    red | 900
03 |  green | 090

INNER JOIN

SELECT * FROM Shirts INNER JOIN Colors ON Shirts.sid = Colors.sid

sid |   name  | color | color_id
---------------------------------
01  | Sample1 |  red  | 900

LEFT JOIN

SELECT * FROM Shirts LEFT JOIN Colors ON Shirts.sid = Colors.sid

sid |   name  | color | color_id
---------------------------------
01  | Sample1 |  red  | 900
02  | Sample2 |  null | null

So in your example you are forcing a row with LEFT JOIN which matches an non-existant condition GENDB_ID which leads to a row with only null values for all columns in the row.

This previous post may also help.

Hope this helps!

score 0 · Accepted Answer · answered Aug 20 '13 at 06:40

Because you're new to inner join and left join, I will get you quickly review.

First, change your query to look like this (you wrong with the field which participate in ON filter)

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid`     //Change this.
WHERE `Shirts`.`sid`='02'

Beside this, when you use left join it does matter which columns you put in where condition (columns from left or right table).

To get correct and expected result, if you must filter your query by columns from RIGHT table (in your case Prices table), then that condition put in ON filter, not in WHERE filter.

Example:

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid`     
WHERE `Prices`.`price`= 100             //This can be wrong

Corrected:

SELECT `Shirts`.*, `Colors`.`color`, `Prices`.`price` FROM `Shirts`
INNER JOIN `Colors`
on `Shirts`.`sid` = `Colors`.`sid`
LEFT JOIN `Prices`
on `Shirts`.`sid` = `Prices`.`sid` AND  `Prices`.`price`= 100   //Correct.
//WHERE `Shirts`.`sid`='02'

Correct Syntax for MySQL Left join

6 Answers6