I have two tables, one is 'posts' the other is 'images'
posts table
id | article |
1 | post 1 |
2 | post 2 |
3 | post 3 |
images table
project_id | image_name|
1 | img1 |
1 | img2 |
2 | img3 |
2 | img4 |
3 | img5 |
3 | img6 |
My current query is this:
SELECT * FROM `images` RIGHT JOIN `posts` ON images.project_id = posts.id
But it is showing multiple results from the posts table.
I want the results like this, to avoid multiple results.
post 1 - img1
post 2 - img3
post 3 - img5
Answer
SELECT
p.article, i.image_name
FROM
`posts` p
JOIN (
select
i2.project_id, min(i2.image_name) as image_name
from
images i2
group by
i2.project_id
) i
on i.project_id=p.id
No comments:
Post a Comment