CODEWITHSUNDEEP
mysqlsqldatabase
Robert H. Nichols
Robert H. Nichols

Reputation: 15

mysql multi row query

Not sure if my title is correct, but let me describe what i am working with.

So I have a view consisting of class_id, entity_id and item_id. Like this:

'21781','4243','15787'
'21781','4249','15793'
'21781','4255','15823'
'21782','4243','15787'
'21782','4249','15793'
'21782','4255','15823'
'21782','4285','15526'
'21782','4288','17588'
'21783','4243','15787'
'21783','4249','15793'
'21783','4255','15823'
'21783','4285','15527'
'21783','4288','17588'
'21784','4243','15787'
'21784','4249','15793'
'21784','4255','15823'
'21784','4285','15527'
'21784','4288','15542'
'21785','4243','15877'
'21785','4249','15793'
'21785','4255','15823'
'21785','4285','15527'
'21785','4288','17588'

So you will note that class_id = 21781, has three entity/item pairs: '4243','15787', '4249','15793', and '4255','15823'. I want to find all those classed that have these same entity/item pairs but not any classes that don't have all three of the same entity/item pair. In other words, if the data set was the one about, the desired query would return class_ids 21782,21783,and 21784, but not 21785.

I've been looking so much that my eyes are crossed. Any ideas from the group-mind?

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Here is one method:

select c.class_id
from class c join
     class c2
     on c2.entity_id = c.entity_id and 
        c2.item_id = c.item_id and
        c2.class_id = 21781 join
     (select count(*) as cnt
      from class
      where class_id = 21781
     ) x
group by c.class_id, x.cnt
having count(*) = x.cnt;

Upvotes: 0

SE1986
SE1986

Reputation: 2750

Try this:

PS, it looks from your code like your IDs are VARCHAR? Not sure if that was just to present on here but I have gone with it. If they are VARCHAR you should look at making them INTs

SET @classid = '21781';
SELECT  c.class_id
FROM    Class c
        LEFT JOIN (
                SELECT  *
                FROM    Class
                WHERE   class_id = @classid
            ) a
            ON a.entity_id = c.entity_id AND a.item_id = c.item_id
GROUP BY c.class_id
HAVING COUNT(a.class_id) = (SELECT COUNT(*) FROM Class WHERE class_id = @classid) AND class_id <> @classid

Upvotes: 1

Related Questions