CODEWITHSUNDEEP
mysqlsql
mpn
mpn

Reputation: 1018

Need to write a slightly advanced query with SQL

So say this is a table:

------------------------------------
|post_id|meta_tag|meta_value       |
------------------------------------
|1      |address |123 Example Lane |
------------------------------------
|1      |name    |John Doe         |
------------------------------------
|1      |email   |[email protected]|
------------------------------------
|3      |foo     |bar              |
------------------------------------

Basically what I want to do is select * from a post_id only if one of itsmeta_tag values has address in it. Then I want to be able to echo out all of the meta_values for each post_id.

Hope that's not too confusing. Thanks.

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

To get all the rows where the post has at least one address tag:

select *
from t t1
where exists (select 1
              from t t2
              where t2.post_id = t1.post_id and t2.meta_tag = 'address'
             );

If you want exactly one, there are various approaches. Following the same logic:

select *
from t t1
where 1 = (select count(*)
           from t t2
           where t2.post_id = t.post_id and t2.meta_tag = 'address'
          );

If you really want only the *post_id*s that have one address tag:

select post_id
from t t1
group by post_id
having sum(t1.meta_tag = 'address') = 1;

(Or make that > 0 if you are looking for any address tag.)

Upvotes: 3

Related Questions