[FIXED] Unable to apply WHERE/AND on MySQL table with 2 columns on MAMP

Issue

I thought I had a very simple query to perform, but I can't seem to make it work.

I have this table with 2 columns:

version_id       trim_id
    1               15
    1               25
    1               28
    1               30
    1               35
    2               12
    2               25
    2               33
    2               48
    3               11
    3               25
    3               30
    3               32

I am trying to get any version-id's that have say a sub-set of trim_id's. Let's say all version_id's that have trim_id's 25 and 30. My obvious attempt was :

SELECT * FROM table WHERE trim_id=25 AND trim_id=30

I was expecting to have version_id 1 and 3 as a result, but instead I get nothing.

I am working with the latest version of MAMP, which has some odd behavior, like in this case it just tells me its 'LOADING' and never gives me an error message or something. But that's normally the case when there is no data to return.

This is InnoDB, if that helps.

Thanks for your input.

Solution

Your query does not work because you are using AND and the trim_id cannot have two different values at the same time, so you need to apply Relational Division to get the result.

You will need to use something similar to the following:

SELECT version_id
FROM yourtable 
WHERE trim_id in (25, 30)
group by version_id
having count(distinct trim_id) = 2

See SQL Fiddle with Demo.

This will return the version_id values that have both 25 and 30. Then if you wanted to include additional columns in the final result, you can expand the query to:

select t1.version_id, t1.trim_id
from yourtable t1
where exists (SELECT t2.version_id
              FROM yourtable  t2
              WHERE t2.trim_id in (25, 30)
                and t1.version_id = t2.version_id
              group by t2.version_id
              having count(distinct t2.trim_id) = 2);

See SQL Fiddle with Demo

Answered By - Taryn