CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Kashif
Kashif

Reputation: 14430

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

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

alt text http://img30.imageshack.us/img30/5668/shippment.png

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.

Upvotes: 2

Views: 910

Answers (4)

jspcal
jspcal

Reputation: 51904

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...

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

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

Upvotes: 0

womp
womp

Reputation: 116977

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

Upvotes: 0

Jay
Jay

Reputation: 57919

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

Upvotes: 0

Related Questions