Stephtheboss Stephtheboss - 6 months ago 11
SQL Question

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.

Answer

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
Comments