"Monty" <mmontreaux_at_hotmail.com> wrote in message
news:1111851483.560395.144730_at_o13g2000cwo.googlegroups.com...
>> "Sybrand Bakker" <postbus_at_sybrandb.removeSPAM.demon.nl> wrote in
>> message news:j5qa41po7ruq6ivkn3i8fa44urt58dr95g_at_4ax.com...
>
>> 2 I'm not sure why you are using a series of 'ANDs' with the
>> activities table joined multiple time, where you could do with only
>> one occurence of this table in the from list, and an OR statement in
>> the where clause. Despite the verbosity of your post, the table
>
> Thank you for your post Sybrand. The reason I am adding the self joins
> is that I want the organisation name returned (from table "contacts")
> to only be returned when all rows in the child table "contactActs" are
> satisfied to contain all the "ContactActID" values (ie 17 and 51), not
> just one of them.
>
> Take the following data for example.
> These use the same table definitions as my original post.
>
> CONTACTS TABLE:
> ====================
> ID ORGNAME
> 100 Company A Ltd
> 101 Organisation B plc
>
> CONTACTACTS TABLE:
> ====================
> ID CONTACTACTID
> 100 17
> 100 51
> 100 23
> 101 17
>
>
> If I just added an "OR" statement such and mentioned the table once, eg
>
> WHERE contactActID=17 OR contactActID=51
>
> I would get both "Company A Ltd" and "Organisation B plc" returned.
>>From this very small bit of data, I would only want 100 - "Company A
> Ltd" as this is the only company with BOTH rows present. Thus the self
> joins, 2 in the above example but up to 6 in my original post.
>
> Hope this makes it more clear.
>
> Thank you
>
> Monty
>
>
Have you tried with GROUP BY ... HAVING ... ?
Received on Sat Mar 26 2005 - 13:27:06 CST
