CODEWITHSUNDEEP
mysqlsql
Rockink
Rockink

Reputation: 180

How to select using join on two conditions for the same column

I am trying to achieve something like this.

id  tag   name
1   foo    name
1   bar    name
2   foo    name
2  foss   name

What I tried to do is. 

select id, group_concat(distinct tag) as tag, group_concat(distinct name) as name
where tag = "foo" or tag = "bar" group by id;

to which I get,

id     tag       name
1    foo, bar  name
2    foo, foss name

But, what I want is only if tag is "foo" and "bar" for same id,then print, which is supposed to give output like this.

id     tag       name
1     foo, bar   name

I did this, as it would work in programming languages like JAVA or C++, by using AND. It results in null..

select id, group_concat(distinct tag) as tag, group_concat(distinct name) as name
where (tag = "foo" and tag = "bar") group by id;

Upvotes: 0

Views: 24

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you want a group by and a having:

select id, group_concat(distinct tag) as tag,
       group_concat(distinct name) as name
from t
where tag in ('foo', 'bar')
group by id
having count(distinct tag) = 2;

As a note. Any programming language that I'm familiar with would return false for tag = 'foo' and tag = 'bar', because tag could have one value or the other, but not both.

Upvotes: 1

Related Questions