List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:June 7 2002 2:34am
Subject:Re: sub-queries
View as plain text  

On Fri 2002-06-07 at 01:55:18 +0200, savage@stripped wrote:
> > Chris,
> >
> > sounds like you're looking for an exclusive left outer join.
> > You want to see records from monitorhosts, where there is no corresponding
> > record
> > in monitorhostgroupdetails, right?
> Yes - and no.  If a HostID 1, assigned to GroupID 1, 3, and 4, then I want
> to issue a query where I specify the GroupID to be 2, and HostID 1 must be
> returned.  All the join statements I got so far, will only include the
> hostID if it is not in monitorhostgroupdetails AT ALL.
> > Use:
> >
> > Select *
> >   from monitorhosts left join monitorhostgroupdetails on
> >  monitorhosts.HostID = monitorhostgroupdetails.HostID
> > where monitorhostgroupdetails.HostID is null
> 1) I can't specify which GroupID I want to exclude, and

I presume you refer to "monitorhostgroupdetails.HostGroupID='1'"?

  ON ... AND monitorhostgroupdetails.HostGroupID='1' WHERE

> 2) I can't limit the hostID on a CompanyID basis (only show hosts
> belonging to a certain company).

  WHERE ... AND monitorhosts.CompanyID = 5

> 3) This now, shows all the hosts that is not in a group at all.  I need to
> specify which hostID, is NOT IN a GroupID.

Isn't this only a variation of 1) ?

> I can't believe that this is so difficult ?!?!?!?!?

No, it's probably not difficult at all. It simply seems as if people
do not understand exactly what you want. A communication problem,

You already got some quite good pointer where to look at (LEFT JOIN,
IS NULL, ...) and probably you just have to do some digging to figure
out the correct query.

Or else, write a more complete example (some sample data + result in a
table form), which shows what you want. This prevents most
ambiguities. The examples you provided earlier were hard to read
(e.g. no column names) and provided to few rows to be unambigous. Or
provide a sub-select that would do what you want.

Just for completeness. The sub-query from your first post:

SELECT monitorhosts.HostID
FROM   monitorhosts
WHERE  monitorhosts.HostID
       NOT IN ( SELECT HostID
                FROM   monitorhostgroupdetails
		WHERE  monitorhostgroupdetails.HostGroupID='1' )

is equivalent to

FROM   monitorhosts 
       LEFT JOIN monitorhostsgroupdetails mhgd
       ON mhgd.HostID = mh.HostID AND
          mhgd.HostGroupID = 1

(only adding your 1) to Kevin's query)



sub-queriesChris Knipe6 Jun
  • Re: sub-queriesKiss D├íniel6 Jun
    • Re: sub-queriesChris Knipe6 Jun
      • RE: sub-queriesCal Evans6 Jun
  • Re: sub-queriesSabine Richter6 Jun
  • Re: sub-queriesChris Knipe6 Jun
  • Re: sub-queriesChris Knipe6 Jun
    • RE: sub-queriesKevin Fries7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun