Hi.
On Fri 2002-06-07 at 01:55:18 +0200, [EMAIL PROTECTED] 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,
IMHO.
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
SELECT mh.HostID
FROM monitorhosts
LEFT JOIN monitorhostsgroupdetails mhgd
ON mhgd.HostID = mh.HostID AND
mhgd.HostGroupID = 1
WHERE mhgd.HostID IS NULL
(only adding your 1) to Kevin's query)
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
