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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > To: "Chris Knipe" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
