CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Vnuk
Vnuk

Reputation: 2703

Filter records based on groups of fields

Here's the thing - I need to filter records based on groups of fields. A prototype would look like this:

select distinct ID, Name from Item i 
inner join (select ItemID from ItemD where ItemDID in (146,147)) idd1 on i.ItemID = idd1.ItemID 
inner join (select ItemID from ItemD where ItemDID in (7641, 7648)) idd2 on i.ItemID = idd2.ItemID

(repeat inner join couple more times)

I know that I can create a stored procedure that uses sp_executesql and feed it those inner joins from my app, but I can't help wondering is there a better solution?

Upvotes: 1

Views: 867

Answers (1)

Andomar
Andomar

Reputation: 238116

You could use a temporary table, probably faster than a lot of joins:

Conditions:  GroupID, ItemDID

And fill it like:

1, 146
1, 147
2, 7641
2, 7648

Then demand that each condition group is satisfied:

select  ID
,       Name
from    Item i
where   not exists
        (
        select  *
        from    Conditions c
        left join    
                ItemID idd
        on      idd.ItemDID = c.ItemDID
                and idd.ItemID = i.ItemID
        group by
                c.GroupID
        having  count(idd.ItemDTD) = 0
        )

(Query not tested; there are many varieties.)

Upvotes: 2

Related Questions