TheFateOfMankind TheFateOfMankind - 1 year ago 76
MySQL Question

Count recursions of cells in one column of a multi column MySQL result

I have the general idea of what I need to do but, no idea that I have seems to works so I don't how to do it.

I'll give an example of what I mean:

Say I'm trying to select media with tag_id's of 4,15 and 9 from the table mediaTable

enter image description here

I want it so I only get film1 but, if I write the statement like this:

SELECT media FROM mediaTable WHERE tag_id = 15 OR tag_id = 4 OR tag_id=9

I get both film1 and book1 and, if I write it with AND instead of OR I don't actually get anything because I believe it is looking for 3 different tag_id columns with the respective tag_id's in one record.

Using the IN statement got me in the exact same position as the OR statement and, with that, I have no more idea except that I may be able to use IF(), albeit I am unsure on how this would be applied.

The results that I am looking for is just film1

Also I am running the queries in PHP 7.0, although I am testing them in PHPMyAdmin just so I can test quicker

Any suggestions will be appreciated. Thanks.

Answer Source

There are several solutions, but the shortest is probably

select media 
from mediaTable 
where tag_id = 15 OR tag_id = 4 OR tag_id=9 
group by media 
having count(distinct tag_id) = 3
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download