How to select a photo from the photo table for each post by query in MySQL

I have two table I want to show one photo for every post but when the run query show post for every photo

Photo table :

+------------------------------------------------------------+
|  photo_id   |    post_id    |    photo_name                |
+------------------------------------------------------------+
|     1       |        1      |    flower.jpg                |
+------------------------------------------------------------+
|     2       |        1      |    book.jpg                  |
+------------------------------------------------------------+
|     3       |        2      |    pen.jpg                   |
+------------------------------------------------------------+
|     4       |        3      |    man.jpg                   |
+------------------------------------------------------------+
|     5       |        3      |    women.jpg                 |
+------------------------------------------------------------+
|     6       |        3      |    boy.jpg                   |
+------------------------------------------------------------+

Post table :

+--------------------------------+
|  post_id    |   post_title     |
+--------------------------------+
|     1       |   flower         |
+--------------------------------+
|     2       |   book           |
+--------------------------------+
|     3       |   pen            |
+--------------------------------+
|     4       |   man            |
+--------------------------------+
|     5       |   women          |
+--------------------------------+
|     6       |   boy            |
+--------------------------------+

use flowing SQL Query and INNER JOIN but my result show like this :

SELECT
Photo.photo_name,
Post.post_title
FROM Photo
INNER JOIN Post
ON Photo.post_id = Post.post_id 
ORDER BY Post.post_id

Result Query after execute :

+-----------------------------+
|  post_title |   photo_name  |
+-----------------------------+
|     flower  |  flower.jpg   |
+-----------------------------+
|     flower  |  flower.jpg   |
+-----------------------------+
|     book    |  book.jpg     |
+-----------------------------+
|     book    |  book.jpg     |
+-----------------------------+
|     pen     |  pen.jpg      |
+-----------------------------+
|     pen     |  pen.jpg      |
+-----------------------------+
|     man     |  man.jpg      |
+-----------------------------+
|     man     |  man.jpg      |
+-----------------------------+
|     women   |  women.jpg    |
+-----------------------------+
|     women   |  women.jpg    |
+-----------------------------+
|     boy     |  boy.jpg      |
+-----------------------------+
|     boy     |  boy.jpg      |
+-----------------------------+

I have 6 posts in post table and I want to get one photo for every post but my result show post by photo count

Answer

You can use group by. Since you have not defined, which photo to use in case of multiple photos for a post, we can get Max() photo for a post:

SELECT
MAX(Photo.photo_name),
Post.post_title
FROM Photo
INNER JOIN Post
ON Photo.post_id = Post.post_id 
GROUP BY Post.post_id, Post.post_title 
ORDER BY Post.post_id

Advertisement

Answer