Re: SQL Query

If the combination of item_id and related_item is unique this query should do the job.

select item_id
from items
group by item_id
having count(*) = (
select count(distinct related_item)
from items)

hth,

Patrick

owais_anjum_at_my-deja.com wrote:

> select distinct ITEM_ID from <table_name> where RELATED_ITEM
> in ('111',222','333');
>
> This is not what I want. IN uses OR condition. This means that I would
> get all such items that have even one of the matching items from the
> list. I want only those items, that have rows against ALL the items in
> the list.
>
> Regards
>
> In article <37A06BA3.7E099985_at_post.pl>,
> Doktor <doktor_at_post.pl> wrote:
> > select distinct ITEM_ID from <table_name> where RELATED_ITEM in
> > ('111',222','333');
> >
> > If You want duplicated values of ITEM_ID , remove DISTINCT word.
> >
> > owais_anjum_at_my-deja.com wrote:
> > >
> > > 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.
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Jul 30 1999 - 11:22:52 CEST