Re: Advice on how to query data

Monty wrote:

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

Sorry if you get part of this twice - had a brain fart.

As you know the number of contactActIDs you're querying, you could do something like:

SELECT *
FROM contacts c
WHERE id IN

      (SELECT x.id
       FROM  (SELECT a.id,
                     a.contactActID
              FROM   contactActs a
              WHERE  a.contactActID IN (17,51)
              GROUP BY a.id, a.contactActID) x
       GROUP BY x.id
       HAVING COUNT(*) = 2 /*the number of contactActIDs*/ )

or in the case of the example in your original post:

SELECT *
FROM contacts c
WHERE id IN

      (SELECT x.id
       FROM  (SELECT a.id,
                     a.contactActID
              FROM   contactActs a
              WHERE  a.contactActID IN (17,51,24,54,43,44)
              GROUP BY a.id, a.contactActID) x
       GROUP BY x.id
       HAVING COUNT(*) = 6)

-- 
Preston.