Opposite of UNION ... 2

[grega]

All, my heads hurting at this one today so I need a bit of help. I have a table with the following data;
[tt]
key val
----------
a 100
a 200
a 300
b 200
c 200
c 300
[/tt]
I want to find all keys which have a val of both 200 and 300, and just cannot find a way. I've tried this ...

select key from table where val=200
union
select key from table where val=300

which will return keys a,b,c. What I need is a way to join the 2 result sets, only including rows present in both sets.

Any ideas much appreciated. I'm running Sybase ASE 11.5.

Greg.

 

[MikeJones]

try intersect instead of union

or..

select a.key from table a
where a.val =200
and exists (select 1 from table b
where a.key = b.key
and a.val = 300)

 

[carp]

Greg -
Try this:

select key from table where val=200
INTERSECT
select key from table where val=300;

It works in Oracle, and I think it's pretty standard SQL.

 

[MalcolmW]

Just for your future reference, besides UNION and UNION ALL;
INTERSECT and INTERSECT ALL, there is also quite often a set operator MINUS (or sometimes EXCEPT). Rarely needed, but handy to know about. Malcolm

 

[Cagliostro]

I think the best sollution is:
select p.key from
(select key from table where val=200) p
inner join
(select key from table where val=300) t
on
p.key=t.key
where ...

John Fill