question

jeffpwi avatar image
jeffpwi asked

more efficient query

create table #Tags ( ID int, Tag varchar(20)) insert into #Tags values (1, 'License A') insert into #Tags values (2, 'License B') insert into #Tags values (3, 'License C') create table #Person (PersonID int, firstname varchar(20), lastname varchar(20)) insert into #Person values (12345, 'Jeff' , 'Smith') insert into #Person values (98765, 'Keith' , 'Jones') create table #PersonTags (TagID int, PersonID int) insert into #PersonTags values (1, 12345) insert into #PersonTags values (2, 12345) insert into #PersonTags values (1, 98765) /******* select everyone from #person that has 'License A' and 'License B' There has to be a more efficient way to write this query???? *********/ select * From #Person p where exists (select 1 from #PersonTags pt , #tags t where pt.PersonID = p.PersonID and pt.TagID = t.ID and t.Tag = 'License A') and exists (select 1 from #PersonTags pt , #tags t where pt.PersonID = p.PersonID and pt.TagID = t.ID and t.Tag = 'License B')
query
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
That's actually a pretty efficient query. If you're worried about the two EXISTS sub-queries, I think they'll turn out to be less costly than most JOIN based methods. Peso and the other "Speed Phreak" contestants might whip you up something really spectacular, but I think the query you supplied is pretty solid.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
How about this: select p.* from #Person p inner join ( select PersonID , count(*) from #PersonTags pt inner join #Tags t on t.TagID = pt.TagID where t.Tag in ('License A', 'License B') group by PersonID having count(*) > 1 ) tag on tag.PersonID = p.PersonID; Disclaimer: You'll get a false positive if anyone has 2 License As or 2 License Bs.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
Another possibility: putting the tag condition into the join operator will produce less reads. SELECT p.PersonID, p.firstname, p.lastname FROM #Person p INNER JOIN #PersonTags pt ON (pt.PersonID = p.PersonID) INNER JOIN #Tags t on PT.TagID = t.ID AND t.tag IN('License A', 'License B') GROUP BY p.PersonID, p.firstname, p.lastname HAVING COUNT(p.PersonID) > 1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
This is another way of writing it, but with the test data and table definition you presented your original post is still the fastest (says my test server). SELECT p.* FROM #Tags t INNER JOIN #PersonTags pt ON t.ID = pt.TagID INNER JOIN #Person p ON p.PersonID = pt.PersonID WHERE T.Tag = 'License A' INTERSECT SELECT p.* FROM #Tags t INNER JOIN #PersonTags pt ON t.ID = pt.TagID INNER JOIN #Person p ON p.PersonID = pt.PersonID WHERE T.Tag = 'License B'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
nice blog Peso, thanks for sharing
0 Likes 0 ·
Peso avatar image Peso commented ·
Thank you Scot. At the moment I am writing a detailed presentation about Relational Division, as I see it's use more and more.
0 Likes 0 ·
Peso avatar image
Peso answered
set nocount on create table #Tags ( ID int, Tag varchar(20)) insert into #Tags values (1, 'License A') insert into #Tags values (2, 'License B') insert into #Tags values (3, 'License C') create table #Person (PersonID int, firstname varchar(20), lastname varchar(20)) insert into #Person values (12345, 'Jeff' , 'Smith') insert into #Person values (98765, 'Keith' , 'Jones') create table #PersonTags (TagID int, PersonID int) insert into #PersonTags values (1, 12345) insert into #PersonTags values (2, 12345) insert into #PersonTags values (3, 12345) insert into #PersonTags values (1, 98765) insert into #PersonTags values (2, 98765) SELECT p.PersonID, p.FirstName, p.LastName FROM #Person AS p INNER JOIN #PersonTags AS pt ON pt.PersonID = p.PersonID INNER JOIN #Tags AS t ON t.ID = pt.TagID GROUP BY p.PersonID, p.FirstName, p.LastName HAVING SUM(CASE WHEN t.Tag IN ('License A', 'License B') THEN 1 ELSE 0 END) = 2 -- The divisor part AND MIN(CASE WHEN t.Tag IN ('License A', 'License B') THEN 1 ELSE 0 END) >= 0 -- The remainder part; >= 1 means EXACTLY License A and License B -- >= 0 means AT LEAST License A and Licence B drop table #Tags, #Person, #PersonTags
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jeffpwi avatar image
jeffpwi answered
All good stuff. THANKS!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.