CODEWITHSUNDEEP
phpmysqlsql
Linesh jose
Linesh jose

Reputation: 53

how to join value from another table by same condition value

I've created two tables named "posts" with fields id,post_name and 'post_meta' with fields id,post_id,meta_name,meta_vale..

Then I've inserted some values like this.

in Post table:-

+----+--------------+
| id |  post_name   |
+----+--------------+
| 15 | fsdfsd       |
| 16 | fsdgbfg fsd  |
+----+--------------+

in Post_meta table:-

+----+---------+-----------+------------+
| id | post_id | meta_name | meta_value |
+----+---------+-----------+------------+
|  1 |      15 | image     | image1.jpg |
|  2 |      15 | image     | image2.jpg |
|  3 |      16 | image     | image3.jpg |
|  4 |      15 | image     | image4.jpg |
+----+---------+-----------+------------+

I want to create a query to get output like this

+----+-------------+-------------+
| id |  post_name  | meta_value  |
+----+-------------+-------------+
| 15 | fsdfsd      | image1.jpg  |
| 16 | fsdgbfg fsd | image3.jpg  |
+----+-------------+-------------+

Only one meta vale along with it's post_id...

Please help me.

Upvotes: 1

Views: 84

Answers (4)

The Humble Rat
The Humble Rat

Reputation: 4696

This should do it:

SELECT pm.id, p.post_name, pm.meta_value
FROM posts p
INNER JOIN post_meta pm ON pm.post_id = p.id
ORDER BY pm.id ASC
GROUP BY pm.post_id

Upvotes: 0

user2995389
user2995389

Reputation: 

Select * from Post_meta, Post where 
(conditions)
Join Post_meta on Post_meta.Post_id = Post.id

Upvotes: 0

rakimo
rakimo

Reputation: 89

SELECT a.id,a.post_name,b.meta_value 
   FROM posts as a 
   INNER JOIN post_meta a p ON a.id=b.post_id

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select m.id, p.post_name, m.meta_value
from posts p
inner join post_meta m on p.id = m.post_id
inner join 
(
   select post_id, min(id) as minid
   from post_meta
   group by post_id
) m2 on m.post_id = m2.post_id and m.id = m2.minid

Upvotes: 1

Related Questions