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

Question

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

this should be achieved by the `left join` you are using. Are you sure you don't miss it because you limit your result to one row only? — spiderman, Nov 08 '16 at 22:12
yes i want to get model information and only one model image which is the highest sorting number. each model has many pictures but i want to display front page only one picture and name .. so i wrote down limit 1. — Daehue Kim, Nov 08 '16 at 22:17
The `LIMIT` clause applies to the whole query, not 1 row per model. — Barmar, Nov 08 '16 at 22:39

score 1 · Answer 1 · answered Nov 08 '16 at 22:23

You are using left join, which is correct and will give you results from model which don't have corresponding row in model_image

However, you are sorting it by a column from the second table. In left join, every row that appears only on the first table, will get null on all columns from the second table.
So as a result, all rows where there's no image will have null as your sorting column. as a result, since you limit your result to 1, you will not get the nulls which are on the bottom of your result set

Thank you for the answer. So is it any way to input "or m.no = null"? — Daehue Kim, Nov 08 '16 at 22:31

score 1 · Accepted Answer · edited May 23 '17 at 10:32

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.

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

2 Answers2