CODEWITHSUNDEEP
mysqlsql-serverleft-join
Stephtheboss
Stephtheboss

Reputation: 65

SQL - Help left join query

I'm trying get datas using an only query but i can't get what i want. I've got 3 tables : I would like to get all photos datas with their max step (id and name). If they don't have it, a null value is ok.

Photo table

photo_id | photo_name
---------------------
1        | A 
2        | B 
3        | C 
4        | D 
5        | E 

Steps table

step_id | step_name  
----------------------
1       | AAA         
2       | BBB         
3       | CCC        
4       | DDD   

photoStep table

id | photo_id | step_id
----------------------
1  | 1        | 1         
2  | 1        | 2         
3  | 1        | 3        
4  | 3        | 1
5  | 5        | 1

The result that I would like to have is that 

photo_id | photo_name | step_id | step_name
-------------------------------------------
1        | A          | 3       | CCC  
2        | B          | NULL    | NULL 
3        | C          | 1       | AAA  
4        | D          | NULL    | NULL 
5        | E          | 1       | AAA

I've tried this query but it misses something because too many lines : 

SELECT * FROM photo p
LEFT JOIN photoStep ps ON ps.photo_id=p.photo_id

I've got this kind of results : 

photo_id | photo_name | step_id | step_name
-------------------------------------------
1        | A          | 1       | AAA  
1        | A          | 2       | BBB  
1        | A          | 3       | CCC  
2        | B          | NULL    | NULL 
3        | C          | 1       | AAA  
4        | D          | NULL    | NULL 
5        | E          | 1       | AAA

Any help is much appreciated. Thanks in advance.

Upvotes: 0

Views: 29

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You need to do a LEFT JOIN on MAX(step_id) of each photo_id:

SELECT
    p.photo_id,
    p.photo_name,
    s.step_id,
    s.step_name
FROM Photo p
LEFT JOIN (
    SELECT
        photo_id, MAX(step_id) AS max_step_id
    FROM photoStep
    GROUP BY photo_id
) ps
    ON ps.photo_id = p.photo_id
LEFT JOIN Steps s
    ON s.step_id = ps.max_step_id

Upvotes: 2

Related Questions