[thelist] help with tricky select statement
rudy
r937 at interlog.com
Sat Jul 20 09:16:11 CDT 2002
> weight is a seperate column and i would like 2 seperate listings,
> one for each entry (entry_num), but I'm only interested in users
> who have both entries
select 'This is the list for entry_num=''1'''
, user_id, emp_num
, weight
from tbl_user_data
where emp_num in
( select emp_num
from tbl_user_data
where entry_num in ('1','2')
group by emp_num
having count(*) = 2 )
and entry_num='1'
select 'This is the list for entry_num=''2'''
, user_id, emp_num
, weight
from tbl_user_data
where emp_num in
( select emp_num
from tbl_user_data
where entry_num in ('1','2')
group by emp_num
having count(*) = 2 )
and entry_num='2'
the subselect gets emp_num for all users that have exactly two rows
but note that this will be satisfied by a user with two '1' rows or two '2'
rows -- presumably you've done something (e.g. declared a unique
constraint) to prevent this
rudy
More information about the thelist
mailing list