CODEWITHSUNDEEP
mysql
user1600117
user1600117

Reputation: 3

Select Distinct values ColumnA while ColumnB = Value1 and ColumnB = Value2

Here's the table

mysql> desc tags;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| content_id | int(11)      | NO   |     | NULL    |                |
| name       | varchar(100) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Some data

mysql> select * from tags;
+----+------------+---------------+
| id | content_id | name          |
+----+------------+---------------+
|  1 |         36 | banana        |
|  2 |         36 | strawberry    |
|  3 |         36 | orange        |
|  4 |         36 | apple         |
|  5 |         36 | watermelon    |
|  6 |         37 | qiwi          |
|  7 |         37 | apple         |
|  8 |         37 | orange        |
|  9 |         37 | bed           |
| 10 |         38 | grape         |
| 11 |         38 | apple         |
+----+------------+---------------+
11 rows in set (0.00 sec)

What I'm wanting is to grab a list of unique content_ids where name is apple AND the content_id ALSO has ORANGE tied to it as well.

So in this example, if I wanted to know which content_ids had been tagged with both "apple" and "orange" it would result in:

+------------+
| content_id |
+------------+
|         36 |
|         37 |
+------------+

Since those are the only two content_ids that have been tagged accordingly. What's the best way to do this?

Upvotes: 0

Views: 140

Answers (3)

AnandPhadke
AnandPhadke

Reputation: 13506

select content_id from fruits 
where name in('apple','orange')
group by content_id
having COUNT(*)=2

Upvotes: 0

Paul Aldred-Bann
Paul Aldred-Bann

Reputation: 6020

You could left join the tables on content_id where it also has the name like so:

select tbl.content_id
from tags tbl
left join tags tbl2 on tbl2.content_id = tbl.content_id and tbl2.name = 'orange'
where tbl.name = 'apple' and tbl2.name is not null
group by tbl.content_id

Only returning rows where there is a corresponding join indicated by the tbl2.name is not null clause.

Upvotes: 1

juergen d
juergen d

Reputation: 204766

select content_id
from tags
where `name` in ('banana', 'orange')
group by content_id
having count(distinct `name`) >= 2

Upvotes: 0

Related Questions