Retrieve unique results from JOIN query

Question

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

Possible duplicate of [MySQL Join Multiple Rows as Columns](http://stackoverflow.com/questions/15462753/mysql-join-multiple-rows-as-columns) — Sh1d0w, Jun 08 '16 at 03:08
Show me the answer that the query give to you. I think it will give you: 1-post 1 -1-img1 //1-post 1 -1-img2//2-post 2 -2-img3//2-post 2 -2-img4 //3-post 3 -3-img5 //3-post 3 -3-img6 — thecassion, Jun 08 '16 at 03:31

Hamza Zafeer · Answer 1 · 2016-06-08T03:16:24.753

0

SELECT * FROM posts RIGHT JOIN images ON posts.id = images.project_id group by posts.id

Use group by Clasue to get unique result.

edited Jun 08 '16 at 03:16

answered Jun 08 '16 at 03:10

Hamza Zafeer

2,360
13
30
42

Its working but I'm unable to pull fields from the images table. – CPuser Jun 08 '16 at 03:15
@CPuser which image do you want to fetch? – Blank Jun 08 '16 at 03:20
From the images table where project_id = id (posts) table I have field called image_name in the images table – CPuser Jun 08 '16 at 03:21
@CPuser glad to know if you accept answer so others can find solution easily. – Hamza Zafeer Jun 08 '16 at 04:20

score 0 · Answer 2 · answered Jun 08 '16 at 03:45

This will do exactly what you asked for.

SELECT `posts`.id, `posts`.`article`,`images`.`image_name` FROM `posts` INNER JOIN `images` ON `posts`.`id` = `images`.`project_id` GROUP BY id

Check this out.

MariaDB [fbb]> SELECT * FROM `posts`;

+----+---------+
| id | article |
+----+---------+
|  1 | post1   |
|  2 | post2   |
|  3 | post3   |
+----+---------+
3 rows in set (0.00 sec)

MariaDB [fbb]> SELECT * FROM `images`;
+------------+------------+
| project_id | image_name |
+------------+------------+
|          1 | img1       |
|          1 | img2       |
|          2 | img3       |
|          2 | img4       |
|          3 | img5       |
|          3 | img6       |
+------------+------------+
6 rows in set (0.00 sec)

MariaDB [fbb]> SELECT `posts`.id, `posts`.`article`,`images`.`image_name` FROM `posts` INNER JOIN `images` ON `posts`.`id` = `images`.`project_id` GROUP BY id
    -> ;
+----+---------+------------+
| id | article | image_name |
+----+---------+------------+
|  1 | post1   | img1       |
|  2 | post2   | img3       |
|  3 | post3   | img5       |
+----+---------+------------+
3 rows in set (0.00 sec)

MariaDB [fbb]>

score 0 · Accepted Answer · answered Jun 08 '16 at 03:54

0

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

answered Jun 08 '16 at 03:54

DeepThought

228
1
3
10

Retrieve unique results from JOIN query

id | article |

project_id | image_name|

3 Answers3