List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:June 6 2002 11:55pm
Subject:Re: sub-queries
View as plain text  
----- Original Message -----
From: "Kevin Fries" <kfries@stripped>
To: "'Chris Knipe'" <savage@stripped>; <mysql@stripped>
Sent: Friday, June 07, 2002 12:38 AM
Subject: RE: sub-queries


> 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
2) I can't limit the hostID on a CompanyID basis (only show hosts belonging
to a certain company).
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.

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

Alternative measures.... Can this be done in two or three different
queries?? I really need to find a solution for this... :-((



> +--------+-----------+-------------------+-------------+--------+
> | 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
> >
> >
>
>
>
> ---------------------------------------------------------------------
> 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-thread111260@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