Comparing SQL Queries That Solve My Thorny SQL Set Operation Problem

For comparison’s sake, the following is a runnable T-SQL script that contains both the solutions provided by helpful friends to my previous “Thorny SQL Set Operation” problem.

set nocount on
go

drop table person
drop table category
drop table personcategory
drop table groupcategory

go

/* create the person table */
create table person (personid int, name varchar(50))
/* create the category table */
create table category (categoryid int, name varchar(50))
/* create the personcategory table */
create table personcategory(personid int, categoryid int)
/* create the group table */
create table groupcategory (groupid int, categoryid int)

go

/* insert the person data */
insert person values (1, “Hillary Clinton”)
insert person values (2, “Segolene Royal”)
insert person values (3, “Barack Obama”)

/* insert the category data */
insert category values (1, “Man”)
insert category values (2, “Woman”)
insert category values (3, “Politician”)
insert category values (4, “American”)
insert category values (5, “French”)

/* insert the personcategory data */
insert personcategory values (1, 2)
insert personcategory values (1, 3)
insert personcategory values (1, 4)
insert personcategory values (2, 2)
insert personcategory values (2, 3)
insert personcategory values (2, 5)
insert personcategory values (3, 1)
insert personcategory values (3, 3)
insert personcategory values (3, 4)

/* insert the group data */

/* GROUP 1: American Politicians */
insert groupcategory values (1, 3)
insert groupcategory values (1, 4)

/* GROUP 2: Female French Politicians */
insert groupcategory values (2, 2)
insert groupcategory values (2, 3)
insert groupcategory values (2, 5)

/* local vars */
declare @american_pols int
select @american_pols = 1
declare @female_french_pols int
select @female_french_pols = 2

/* QUERY METHOD 1: “Hans Method” */

select distinct p.*
from Person p
join PersonCategory c on (p.personid = c.personid)
where c.personid in (
select c.personid from PersonCategory c
where c.categoryid in (
select g.categoryid from GroupCategory g
where g.groupid = @female_french_pols
)
group by c.personid
having count(*) = (
select count(*) from GroupCategory g
where g.groupid = @female_french_pols
)
)
and c.categoryid in (
select gc.categoryid from GroupCategory gc
where gc.groupid = @female_french_pols
)

/* QUERY METHOD 2: “Tom Music / Brian Dorsey Method” */

select p.personid, p.name, count(*) from person p
join personcategory pc on pc.personid = p.personid
join category c on c.categoryid = pc.categoryid
where c.categoryid in (select gc.categoryid from groupcategory gc where groupid = @female_french_pols)
group by p.personid, p.name
having count(*) = (select count(gc.categoryid) from groupcategory gc where groupid = @female_french_pols)
order by p.personid

Advertisements

1 Response to “Comparing SQL Queries That Solve My Thorny SQL Set Operation Problem”


  1. 1 Tom Music May 28, 2008 at 1:02 pm

    In further digging into the Hans method, it looks both methods do the “count how many Categories are in the Group, and compare that to how many of the group’s Categories are held by the Person”.

    One thing that confuses me about the Hans method is the need of the WHERE on c.categoryid at the end. It seems like the inner SELECT statements take care of making sure we’re only counting germane categories — by the time we get back to the root JOIN we have a neat list of PersonIDs that qualify.

    Other than that, it looks like great minds are thinking alike!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: