Re: SQL Query

From: Craig Ledbetter <craigl_at_gte.net>
Date: Tue, 03 Aug 1999 06:22:02 GMT
Message-ID: <eUvp3.1188$p5.53507@dfiatx1-snr1.gtei.net>

If the list of related items is not huge, this will work:

SELECT
item_id
FROM
  (SELECT

     item_id,
     MAX(DECODE(related_item,111,1,0)) AS match_111,
     MAX(DECODE(related_item,222,1,0)) AS match_222,
     MAX(DECODE(related_item,333,1,0)) AS match_333
   FROM
     your_table
   WHERE
     related_item IN (111,222,333)
   GROUP BY
     item_id

   )
WHERE

   match_111 = 1 AND
   match_222 = 1 AND
   match_333 = 1;

If the list of related items is extremely long, then this may not be the best solution, as it creates a crosstab dataset to solve the problem. (Run the subquery only to see this.)
CSL <owais_anjum_at_my-deja.com> wrote in message news:7npo36$lqq$1_at_nnrp1.deja.com...
> Hello
>
> I have a table with the following structure and data:
>
> ITEM_ID RELATED_ITEM
> ==========================
> aaa 111
> aaa 222
> aaa 333
> bbb 111
> bbb 777
> xxx 909
>
> The query I want should return all the item_id's against a given list of
> related_items. e.g. I want an item_id which has all of the listed
> related_items ('111',222','333').
>
> I thought ALL was meant for such scenarios...but I've not had any
> success with it so far.
>
> Waiting for opinions
> Owais
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Aug 03 1999 - 01:22:02 CDT