List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:June 6 2002 8:52pm
Subject:Re: sub-queries
View as plain text  
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