List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:June 5 2002 7:43pm
Subject:Re: Comparing array elements
View as plain text  
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" <dnelson@stripped>
To: "Chris Knipe" <savage@stripped>
Cc: <php-general@stripped>; <mysql@stripped>
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
> dnelson@stripped
>

Thread
Comparing array elementsChris Knipe5 Jun
  • Re: [PHP] Comparing array elementsPushkar Pradhan5 Jun
  • Re: Comparing array elementsDan Nelson5 Jun
  • Re: Comparing array elementsChris Knipe5 Jun