[thelist] help with tricky select statement

[rudy]

> 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