A
Anne Savage
Hi -
I need to ask the question "Which people are on ALL of
these Lists". There's a people table, a list table and a
peoplelist xref table.
I know at least 4 ways to do this, two are quite fast and
two quite slow in my limited testing. The two fast ones
are below as Way1 & Way2 and the slower ones as Way3 &
Way4.
Does any one know any reason that Way1 would be better
than Way2 or vice versa? Or is there another, better way?
Ultimately this solution will be coded behind a form with
a multiple select list box, and an AND/OR radio button.
Obviously, this is the AND solution.
Any thoughts? Thanks for your help.
Way 1: Use Group By & Having Count =
(pseudo code)
SELECT PeopleID FROM PeopleList
WHERE (ListID In 1,2,3)
GROUP BY PeopleID
HAVING Count(PeopleListID=3);
Way 2: Use subquery for each list and inner join
(pseudo code)
SELECT DISTINCT list1.PeopleID
FROM ((Select PeopleID from PeopleList where ListID=1) as
List1
INNER JOIN (Select PeopleID from PeopleList where
ListID=2) as List2 ON
List1.PeopleID = List1.PeopleID)
INNER JOIN (Select PeopleID from
PeopleList where ListID=2) as List3 ON List2.PeopleID =
List3.PeopleID
Way3: Exists and correlated subqueries (I've found this is
a lot slower than Way1 & Way2)
(pseudo code)
SELECT PeopleID
FROM People
WHERE Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 1)
AND Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 2)
AND Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 3)
Way4 In & Subqueries (Again, much slower than Way1 & Way2)
SELECT PeopleID
FROM People
WHERE PeopleID In
(Select PeopleID from ListPeople where ListID = 1)
AND PeopleID In
(Select PeopleID from ListPeople where ListID = 2)
AND PeopleID In
(Select PeopleID from ListPeople where ListID = 3)
I need to ask the question "Which people are on ALL of
these Lists". There's a people table, a list table and a
peoplelist xref table.
I know at least 4 ways to do this, two are quite fast and
two quite slow in my limited testing. The two fast ones
are below as Way1 & Way2 and the slower ones as Way3 &
Way4.
Does any one know any reason that Way1 would be better
than Way2 or vice versa? Or is there another, better way?
Ultimately this solution will be coded behind a form with
a multiple select list box, and an AND/OR radio button.
Obviously, this is the AND solution.
Any thoughts? Thanks for your help.
Way 1: Use Group By & Having Count =
(pseudo code)
SELECT PeopleID FROM PeopleList
WHERE (ListID In 1,2,3)
GROUP BY PeopleID
HAVING Count(PeopleListID=3);
Way 2: Use subquery for each list and inner join
(pseudo code)
SELECT DISTINCT list1.PeopleID
FROM ((Select PeopleID from PeopleList where ListID=1) as
List1
INNER JOIN (Select PeopleID from PeopleList where
ListID=2) as List2 ON
List1.PeopleID = List1.PeopleID)
INNER JOIN (Select PeopleID from
PeopleList where ListID=2) as List3 ON List2.PeopleID =
List3.PeopleID
Way3: Exists and correlated subqueries (I've found this is
a lot slower than Way1 & Way2)
(pseudo code)
SELECT PeopleID
FROM People
WHERE Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 1)
AND Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 2)
AND Exists (Select PeopleID from ListPeople where
People.PeopleID =
ListPeople.PeopleID and ListID = 3)
Way4 In & Subqueries (Again, much slower than Way1 & Way2)
SELECT PeopleID
FROM People
WHERE PeopleID In
(Select PeopleID from ListPeople where ListID = 1)
AND PeopleID In
(Select PeopleID from ListPeople where ListID = 2)
AND PeopleID In
(Select PeopleID from ListPeople where ListID = 3)