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:[EMAIL PROTECTED]] > Sent: Thursday, June 06, 2002 1:52 PM > To: [EMAIL PROTECTED] > 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" <[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
