CODEWITHSUNDEEP
mysql
user1813619
user1813619

Reputation: 31

pulling ID from column 1, where column 2 has multiple values

I am trying to display 'id', 'topic_id' where an 'id' may have two specific values from column two, in one table. however, some id's have multiple values.

id    topic_id
1     red
2     blue
1     blue
4     purple

if i want to display the id's where topic_id is BOTH red and blue, what is the query?

select id, topic_id
from db
where topic_id='red, blue';

the result set is "1,2" instead of just 1, which has BOTH red and blue.

thanks!!!

Upvotes: 0

Views: 257

Answers (3)

Yogendra Singh
Yogendra Singh

Reputation: 34397

Are you looking for result like 1 red blue, 2 blue, 4 purple i.e. if multiple topic ids concat them? If so, please try using GROUP_CONCAT function as below:

   SELECT id, GROUP_CONCAT(topic_id separator ' ')
   FROM db
   WHERE topic_id in ('red', 'blue')
   GROUP BY id
   HAVING count(id) = 2;

If you want the topic Id values separated by comma (,) then:

   SELECT id, GROUP_CONCAT(topic_id separator ', ')
   FROM db
   WHERE topic_id in ('red', 'blue')
   GROUP BY id
   HAVING count(id) = 2;

Upvotes: 1

Taryn
Taryn

Reputation: 247880

I believe this is the query that you want:

select id
from yourtable
where topic_id in ('blue', 'red')
group by id
having count(distinct topic_id) > 1

See SQL Fiddle with Demo

Or 

select id
from yourtable
where topic_id in ('blue', 'red')
group by id
having count(distinct topic_id) = 2

See SQL Fiddle with Demo

Upvotes: 2

mcriecken
mcriecken

Reputation: 3297

You can use the following query. The nested SELECT query is going to return all IDs that have the topic_id 'blue'. It's then going to look at that list of IDs and if any of them also have a topic_id of 'red', then they will be returned in your result set.

SELECT id
FROM table1
WHERE id IN (SELECT id FROM table1 WHERE topic_id ='blue')
AND topic_id = 'red';

DEMO

Upvotes: 1

Related Questions