Re: sql query where criteria is not all contained in each row

John,

depending on the performance, you have at least those two options:

  select *
  from invoice_detail invd1
  where exists

( select null

        from invoice_detail invd2
        where invd2.invoice_id = invd1.invoice_id
        and invd2.product_id = '002'
      )
  and exists


      ( select null


        from invoice_detail invd3
        where invd3.invoice_id = invd1.invoice_id
        and invd3.product_id = '005'
      );

The "select null" can be replaced by anything, like " select 'dummy' " if that would make it better readible in your opinion. Another option:

  select *
  from invoice_detail invd1
  where 2 =

( select count( distinct ( invd2.product_id ) )

        from invoice_detail invd2
        where invd2.invoice_id = invd1.invoice_id
        and invd2.product_id in ( '002', '005' )
      );

Arjan. Received on Sun Aug 02 1998 - 14:33:21 CDT