trouble with intersect

[KJS2002]

I have a simple table containing personID-choiceID pairs. I want to retrieve PersonIDs that have opted for one choice and another and another etc... Using an intersect query may not be the best way but ...

(SELECT * from <table> WHERE choiceID= [variable])
intersect
(SELECT * from <table> WHERE choiceID= [another variable]);

doesn't seem to work. (A straight UNION does).

What am I doing wrong?
Would using WHERE EXISTS make more sense?
Will this work in with mySQL as well?

 

[MeanGreen]

This is the Microsoft SQL forum. We do not have an INTERSECT syntax in our T-SQL language (other than as a reserved word) that I am aware of. You may want to post this in the appropriate forum.

Sorry.

 

[r937]

with regard to syntax, expecting microsoft to support standard sql is a long shot, but taking your question to the ansi sql forum may just result in frustration, as those guys will probably ask what database you're using and send you back here, eh

with regard to intended results, INTERSECT would not return any rows if you use &quot;select star&quot; because there wouldn't be any duplicate rows between subqueries, since the subqueries would have different values of ChoiceID (holler if this didn't make sense)

you can accomplish what you want with inner joins

[tt]select t1.personID
from simpletable t1
inner
join simpletable t2
on t1.personID = t2.personID
where t1.choiceID = value1
and t2.choiceID = value2[/tt]

you can also use WHERE EXISTS subqueries

rudy