Daehue  Kim Daehue Kim - 27 days ago 7
MySQL Question

mysql join query. however, some data doesn't have a joined data

I want to make a query.

what i exactly want to do is ~

i have two table one is called

model
, another is called
model_image
.

i want to select
no
,
name
,
mobile
from
model
and
img_name
from
model_image
if data exist.

select m.no, m.name, m.display_name, m.mobile, i.img_name
from model as m
left join model_image as i on m.no = i.model_no
order by i.sort desc
limit 1


So, I made this query but I have no idea about how can I get data which doesn't have any data in model_image table.

Anyone can help me ?

Thank you ~

Following query works Thank you ~

select m.no, m.name, m.display_name, m.mobile, IFNULL(i.img_name, '') AS img_name
from model as m
left join (
SELECT a.model_no, a.img_name
FROM model_image AS a
JOIN (SELECT model_no, MAX(sort) AS maxsort
FROM model_image
GROUP BY model_no) AS b
ON a.model_no = b.model_no AND a.sort = b.maxsort) AS i
on m.no = i.model_no


enter image description here

Answer

If you want to get the highest sorting image per model, you need to do that in a subquery, and join that with the model table.

select m.no, m.name, m.display_name, m.mobile, IFNULL(i.img_name, '') AS img_name
from model as m
left join (
    SELECT a.model_no, a.img_name
    FROM model_image AS a
    JOIN (SELECT model_no, MAX(sort) AS maxsort
          FROM model_image
          GROUP BY model_no) AS b
    ON a.model_no = b.model_no AND a.sort = b.maxsort) AS i
on m.no = i.model_no 

See SQL Select only rows with Max Value on a Column for an explanation of the subquery.

Comments