Re: Advice on how to query data

On 26 Mar 2005 07:38:03 -0800, "Monty" <mmontreaux_at_hotmail.com> wrote:

>> 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

It does. What I can come up with right now (and we are going to DST this night, and as it is Easter I have to leave my home very early), is that a series of EXISTS subqueries should work more efficiently. I know it is cumbersome, that is why I said, what I can come up with right now.

select
from maintable
where exists
(select 'x'
 from child
where fk = alternative 1)
and
exists
etc.

Alternatively (thinking of this right now)

select * from maintable
where <number of alternatives> =
(select count(*)
 from childtable
 where fk in (alternative 1, alternative 2, alternative 3, etc...)

Hth

--
Sybrand Bakker, Senior Oracle DBA