Monday, March 26, 2018

php - Retrieve unique results from JOIN query




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

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...