How to combine an OR statement

[barkley1979]

Using MS Query to extract data from our SQL database into Excel. Here is an excerpt from a piece of my SQL code:

...
WHERE
(WODRAW.MENUOPTION='11') OR (WODRAW.MENUOPTION='02')
...

I have a primary key of WONUM. The code above will obviously bring out any lines where MENUOPTION is 11 or 02. This results single lines and multiple lines.
I need to be able to show only records (WONUM) where there is a MENUOPTION 11 and 02.

For example:

WONUM MENUOPTION
12456 11
12456 02
12467 11
12468 02
12469 02

Is the data I currently receive.

I want to only see

WONUM MENUOPTION
12456 11
12456 02

Any help would be appreciated.

 

[grega]

Tricky (IMO)

select a.wonum, a.menuoption
from wodraw a, wodraw b
where a.menuoption = '02'
and   a.wonum = b.wonum
and   b.wonum = '11'
union
select b.wonum, b.menuoption
from wodraw a, wodraw b
where a.menuoption = '02'
and   a.wonum = b.wonum
and   b.wonum = '11'

Obviously this is very specific code. If you need somethin more generic then we can try something else.

Greg.

 

[rrrkrishnan]

Try this.
select a.wonum, a.menuoption
from wodraw a
where a.menuoption in ('11','02')
and exists (select * from wodraw b
where b.wonum=a.wonum
and b.menuoption in ('11','02')
and b.menuoption <> a.menuoption)

 

[PHV]

I need to be able to show only records (WONUM) where there is a MENUOPTION 11 and 02.
Have you tried this ?[tt]
Select WONUM From WODRAW
Where MENUOPTION In ('11','02')
Group By WONUM
Having Count(*)>1[/tt]


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884