Using (IN operator) OR condition in Where clause as AND condition.

Question

Please look at following image, I have explained my requirements in the image.

I can't use here WHERE UsageTypeid IN(1,2,3,4) because this will behave as an OR condition and fetch all records.

I just want those records, of first table, which are attached with all 4 ShipmentToID .

All others which are attached with 3 or less ShipmentToIDs are not needed in result set.

Thanks.

Answer 1

if (EntityId, UsageTypeId) is unique:

select s.PrimaryKeyField, s.ShipmentId from shipment s, item a
where s.PrimaryKeyField = a.EntityId and a.UsageTypeId in (1,2,3,4)
group by s.PrimaryKeyField, s.ShipmentId having count(*) = 4

otherwise, 4-way join for the 4 fields,

select distinct s.* from shipment s, item a,  item b, item c, item d where
s.PrimaryKeyField = a.EntityId = b.EntityId = c.EntityId = d.EntityId and
a.UsageTypeId = 1 and b.UsageTypeId = 2 and c.UsageTypeId = 3 and
d.UsageTypeId = 4

you'll want appropriate index on (EntityId, UsageTypeId) so it doesn't hang...

Answer 2

This way is preferred to the multiple join against same table method, as you can simply modify the IN clause and the COUNT without needing to add or subtract more tables to the query when your list of IDs changes:

select EntityId, ShipmentId
from (
    select EntityId
    from (
        select EntityId
        from EntityUsage eu
        where UsageTypeId in (1,2,3,4)
        group by EntityId, UsageTypeId
    ) b
    group by EntityId
    having count(*) = 4
) a
inner join Shipment s on a.EntityId = s.EntityId

Answer 3

If there will never be duplicates of the UsageTypeId-EntityId combo in the 2nd table, so you'll never see:

EntityUsageTypeId | EntityId | UsageTypeId
22685 | 4477 | 1
22687 | 4477 | 1

You can count matching EntityIds in that table.

WHERE (count(*) in <tablename> WHERE EntityId = 4477) = 4

Answer 4

DECLARE @numShippingMethods int;
SELECT @numShippingMethods = COUNT(*) 
FROM shippedToTable;

SELECT tbl1.shipmentID, COUNT(UsageTypeId) as Usages
FROM tbl2 JOIN tbl1 ON tbl2.EntityId = tbl1.EntityId
GROUP BY tbl1.EntityID
HAVING COUNT(UsageTypeId) = @numShippingMethods