CODEWITHSUNDEEP
mysqlsql
Juan Jo
Juan Jo

Reputation: 892

inner join depending on rows

I have a table posts with, for example:

post_id | title
   1       im in Guadalajara and Zapopan
   2       im just in Guadalajara but not in Zapopan

and the table meta_keys

post_id | meta_key
  1         Guadalajara
  1         Zapopan
  2         Guadalajara

I need to get the posts that are in Guadalajara and zapopan BOTH, I realize that if I make an inner join with or, will give me both

in this example I would be expecting to construct a query that throws the post 1 (just the 1)

Thanks

Upvotes: 0

Views: 76

Answers (2)

Ben
Ben

Reputation: 52863

The following gets you the post_ids that are in both

select post_id
  from meta_keys
 where meta_key in ('Guadalajara','Zapopan')
 group by post_id
having count(*) = 2

You can then join it to posts to get the actual posts:

select p.*
  from posts as p
  join ( select post_id
           from meta_keys
          where meta_key in ('Guadalajara','Zapopan')
          group by post_id
         having count(*) = 2 ) as m
    on p.post_id = m.post_id

Upvotes: 1

John Feminella
John Feminella

Reputation: 311526

One approach is to use a double join:

select
  *
from
  posts p
inner join meta_keys mk1 on p.post_id = mk1.post_id
inner join meta_keys mk2 on p.post_id = mk2.post_id
where
  mk1.meta_key = 'Guadalajara' and
  mk2.meta_key = 'Zapopan'
;

Upvotes: 1

Related Questions