Hi Dan, Close to what I want.... But no cigar just yet. Thanks for the help, but I think you mis-understood some parts of it. First things first, on creating the list of servers which is in a group - thanks, that works beautifully and I got it working. The query looks as follows:
SELECT monitorhosts.HostID FROM monitorhosts LEFT JOIN monitorhostgroupdetails ON monitorhosts.HostID=monitorhostgroupdetails.HostID WHERE monitorhostgroupdetails.HostGroupID='1'; The WHERE clause simply specify which group I want to extract a server list for. However, selecting the items not in a group, only returns items that is not in a group AT ALL, and not all the items that isn't in a specific group as I want... Here's the layout of the two tables for you: Items Table (+- 10,000 Records - monitorhosts) +----------------------+--------------+------+-----+---------+-------------- --+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------+-------------- --+ | HostID | tinyint(4) | | PRI | NULL | auto_increment | | CompanyID | tinyint(4) | | MUL | 0 | | +----------------------+--------------+------+-----+---------+-------------- --+ 2 rows in set (0.08 sec) Groups Table (+- 10 Records - monitorhostgroupdetails) +-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | HostGroupDetailID | mediumint(9) | | PRI | NULL | auto_increment | | HostID | mediumint(9) | | MUL | 0 | | +-------------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.76 sec) Now, I have 10,000 items. 1-1000 is assigned in group1, 500-1500 is assigned to group2, and 1500-10,000 is in Group3. I want to be able to use join queries, where I can take group1, generate a list of all the items in the group, and then generate a list of items not in group1. The same goes for group2, group3, groupX. Item's not in a group at all, must also be returned... One item can also be assigned to more than one group, which just adds to the complexity here I think :-( Basically, I want anything that is in group1 and, the rest..... I've tried things like (to get items not in group1 for this example): SELECT monitorhosts.HostID FROM monitorhosts LEFT JOIN monitorhostgroupdetails ON monitorhosts.HostID=monitorhostgroupdetails.HostID WHERE monitorhostgroupdetails.HostID <> '1' OR monitorhostgroupdetails.HostID IS NULL; But that doesn't return the desired result :(( Also seeing that the answer to my question somehow lies in the LEFT / RIGHT JOIN statement, I went to have a read in the manual about the function / syntax... I didn't understand ANY of it :-( and I got a killer headache now as well, which just isn't helping right now... *shrugs* Any ideas? ----- Original Message ----- From: "Dan Nelson" <[EMAIL PROTECTED]> To: "Chris Knipe" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, June 05, 2002 7:45 PM Subject: Re: Comparing array elements > In the last episode (Jun 05), Chris Knipe said: > > Does anyone have and ideas of wisdom on how to compare arrays (mysql > > result sets) and group items based on if they exist in two or more > > arrays? > > > > Now, I want to compare group1 against the server list, and do something like > > the following: > > In group: > > server1 > > server4 > > server5 > > SELECT servers.server FROM servers LEFT JOIN group1 ON > servers.server=group1.server WHERE group1.server IS NOT NULL; > > > > Not in group: > > server2 > > server3 > > serverX > > SELECT servers.server FROM servers LEFT JOIN group1 ON > servers.server=group1.server WHERE group1.server IS NULL; > > -- > Dan Nelson > [EMAIL PROTECTED] > --------------------------------------------------------------------- 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
