| Nested query [message #19307] |
Mon, 11 March 2002 06:02  |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
Hello All,
I have the following query that returns 7 rows:
select count(*) from events e, event_hist eh
where e.num = eh.num
and eh.aff = 2
and e.num = 3003
and eh.time >= '06-FEB-02'
and eh.time < '07-FEB-02';
Now I want to select all the ses_id's (there are supposed to be duplicates) that have at least one e.num of 3003 in the group. I did this manually by ses_id and came up with a number of 58, but I'm not sure how to get this nested query to work. The common field between both tables is the num field, ses_id is only in the eh table.
What I'm looking to do is to select all four xyz ses_id
rows if one of the rows has a num of 3003 and ignore the rest, like the example below:
num ses_id
2000 syx
3000 xyz
3003 xyz
4000 xyz
3000 syx
1000 xyz
5000 xxx
4000 eee
3000 yyy
output:
num ses_id
1000 xyz
3000 xyz
3003 xyz
4000 xyz
Any help is appreciated!
Thanks,
James
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
