----- Original Message ----- From: "Kevin Fries" <[EMAIL PROTECTED]> To: "'Chris Knipe'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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:[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 > > --------------------------------------------------------------------- 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
