List:General Discussion« Previous MessageNext Message »
From:Kevin Fries Date:June 6 2002 10:38pm
Subject:RE: sub-queries
View as plain text  
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?

Use:

Select *
  from monitorhosts left join monitorhostgroupdetails on
 monitorhosts.HostID = monitorhostgroupdetails.HostID
where monitorhostgroupdetails.HostID is null
;
+--------+-----------+-------------------+-------------+--------+
| hostID | CompanyID | HostGroupDetailID | HostGroupID | HostID |
+--------+-----------+-------------------+-------------+--------+
|      7 |         1 |              NULL |        NULL |   NULL |
+--------+-----------+-------------------+-------------+--------+

The trick is the "is null" which trims the result set to only show those
join-results which
could find no right-hand record to join to.

hth,
Kevin

> -----Original Message-----
> From: Chris Knipe [mailto:savage@stripped]
> Sent: Thursday, June 06, 2002 1:52 PM
> To: mysql@stripped
> Subject: Re: sub-queries
>
>
> Ok, I spoke to soon... And this is starting to drive me up
> the walls now...
> i.e. getting REALLY irritating and frustrating.
>
> mysql> DESCRIBE monitorhosts;
> +----------------------+--------------+------+-----+---------+
> --------------
> --+
> | Field                | Type         | Null | Key | Default | Extra
> |
> +----------------------+--------------+------+-----+---------+
> --------------
> --+
> | HostID               | tinyint(4)   |      | PRI | NULL    |
> auto_increment |
> | CompanyID            | tinyint(4)   |      | MUL | 0       |
> |
> +----------------------+--------------+------+-----+---------+
> --------------
> --+
> 2 rows in set (0.29 sec)
>
> mysql> DESCRIBE monitorhostgroupdetails;
> +-------------------+--------------+------+-----+---------+---
> -------------+
> | Field             | Type         | Null | Key | Default |
> Extra          |
> +-------------------+--------------+------+-----+---------+---
> -------------+
> | HostGroupDetailID | mediumint(9) |      | PRI | NULL    |
> auto_increment |
> | HostGroupID       | mediumint(9) |      | MUL | 0       |
>              |
> | HostID            | mediumint(9) |      | MUL | 0       |
>              |
> +-------------------+--------------+------+-----+---------+---
> -------------+
> 3 rows in set (0.02 sec)
>
> mysql>
>
> Now, here's the catch.... The HostID field from monitorhosts,
> is a itemID
> for a server entry.  This server entry is unique, can be in
> one, multiple or
> even in no group AT ALL.
>
> monitorhostgroupdetails maps a hostID to a groupID, and
> consist of a unique
> ID (i.e. only one unique hostID is allowed in one
> GroupID)....  All the
> statements I got so far, list only the hostID if they are in
> a group... Not
> if they aren't in a group AT ALL.
>
> monitorhosts table data:
> |      1 |         1 |
> |      7 |         1 |
>
> >From this, I can see that I have HostID 1 & 7, assigned to
> CompanyID 1.
>
> monitorhostgroupdetails table data:
> |                51 |           1 |      1 |
>
> Here, I can see HostID 1 belongs to HostGroup 1.  The results
> on all the
> various queries I tried and that's been suggested...
>
> Select monitorhosts.HostID
>   from monitorhosts left join monitorhostgroupdetails on
> monitorhostgroupdetails.HostID = monitorhosts.HostID
>  where monitorhostgroupdetails.HostGroupID !='1';
>
> This returns no data from the monitorhosts table, even through we have
> HostID which isn't in a group.  When HostGroupID  is changed
> to 2, HostID 1
> is returned (because it is in the hostgroup table), but
> hostid 7 which isn't
> in a group, is still left out of the equasion.
>
> SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM
> monitorhosts,
> monitorhostgroupdetails WHERE monitorhosts.HostID =
> monitorhostgroupdetails.HostID AND
> monitorhostgroupdetails.HostGroupID !=
> '1' AND monitorhosts.CompanyID='1';
>
> Nothing is returned.  My gut tells me the same happens as
> with the previous
> query.
>
> In otherwords, the queries will *only* return any hostID
> data, if the host
> is in the monitorhostgroupdetails table, which, isn't going
> to be right.  A
> host is registered in the database, and only certain hosts,
> under certain
> curcumstances is grouped.  Basically, what I want to do now,
> is that when a
> client modifies the servers assigned in a group, I only want
> to list the
> servers which is NOT allready in that specific group.....
>
> Is this possible, or am I really going to have to use PHP
> arrays and compare
> arrays with hundreds of thousands of values in them??  *deep sigh*
>
>
> ----- Original Message -----
> From: "Chris Knipe" <savage@stripped>
> To: <mysql@stripped>
> Sent: Thursday, June 06, 2002 10:16 PM
> Subject: Re: sub-queries
>
>
> > *MWAH*!!!!!!!!!!!
> >
> > Thanks a million, tested and working beautifully.... Can't
> believe in two
> > days I didn't think of this...
> >
> > --
> > me
> >
> >
> > ----- Original Message -----
> > From: "Sabine Richter" <sabine@stripped>
> > To: "Chris Knipe" <savage@stripped>
> > Cc: <mysql@stripped>
> > Sent: Thursday, June 06, 2002 10:14 PM
> > Subject: Re: sub-queries
> >
> >
> > > SELECT monitorhosts.HostID
> > > FROM monitorhosts, monitorhostgroupdetails
> > > WHERE monitorhosts.HostID =  monitorhostgroupdetails.HostID
> > > AND monitorhostgroupdetails.HostGroupID != '1');
> > >
> > > Gruss
> > > Sabine
> > >
> > > Chris Knipe wrote:
> > > >
> > > > Lo all,
> > > >
> > > > are sub-queries supported on mysql-max 3.23.49 ??
> > > >
> > > > If they are, what's wrong with the following statement?
> > > >
> > > > SELECT monitorhosts.HostID
> > > >     FROM monitorhosts
> > > >    WHERE monitorhosts.HostID NOT IN
> > > >          (SELECT HostID
> > > >             FROM monitorhostgroupdetails
> > > >            WHERE monitorhostgroupdetails.HostGroupID='1');
> > > >
> > > > mysql complains about a syntax error right at the
> begining of the
> second
> > > > select...
> > > >
> > > > ERROR 1064: You have an error in your SQL syntax near
> 'SELECT HostID
> > > >             FROM monitorhostgroupdetails
> > > >            WHERE monitorh' at line 4
> > > >
> > > > ty
> > > >
> > > >
> ---------------------------------------------------------------------
> > > > 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
> <mysql-thread111241@stripped>
> > > > To unsubscribe, e-mail
> > <mysql-unsubscribe-sabine=textraeume.de@stripped>
> > > > Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread111246@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-savage=savage.za.org@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
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