Hello.
On Fri 2002-06-07 at 05:15:51 +0200, [EMAIL PROTECTED] wrote:
[...]
> > No, it's probably not difficult at all. It simply seems as if
> > people do not understand exactly what you want. A communication
> > problem, IMHO.
>
> Kevin was also kind enough to point this out to me, and I have to
> agree with both of you. As I did explain to Kevin in private
> however, I do have very
Never mind. I was just trying to explain why nobody came up with the
right solution at once.
> If anyone feel like taking 10 minutes of their time, and explaining
Well, probably more than 10 minutes. ;-)
> exactly what the query does, I'll appreciate it allot. It may help
> me understand the basic logic behind the structure of the query, and
> aid me in the future when I may need to execute such queries again
> (although, I'm honestly hoping that when such a time comes, MySQL
> will support sub-queries).
Okay. Let's see the query again, a bit reformatted:
SELECT mh.HostID, mh.HostDescription
FROM monitorhosts mh
LEFT JOIN monitorhostgroupdetails mhgd ON
mh.HostID = mhgd.HostID AND
mhgd.HostGroupID = 2
WHERE mhgd.HostID IS NULL AND
mh.CompanyID = 1;
First, I assume that it is known that a normal JOIN (written with ',')
builds a cross product of the two tables, i.e. build pairs of each
record from the first table with each record of the second table.
Then, you normally have something like
WHERE mh.HostID = mhgd.HostID
which only chooses those pairs, which have matching HostIDs. A LEFT
JOIN does the same, but for all records of the left (=first) table,
which have no match in the right table, it will insert NULL for the
right table values. I.e. if you have (from above)
LEFT JOIN ... ON mh.HostID = mhgd.HostID
you will get the result from a normal JOIN (all pairs for which
mh.HostID = mhgd.HostID is true) and all remaining records from
monitorhosts (all for whose HostID was no record in
monitorhostgroupdetails) paired with NULL values for the columns of
monitorhostgroupdetails.
Another way to see this is to take all records from the left table and
pair them with either the matching records from the right table or
with NULL values if record matched.
An additional "mhgd.HostGroupID = 2" in the ON clause will only
consider a pair valid, if "mhgd.HostGroupID = 2" (as in a normal join)
and for all non-fitted records of the left table it pairs them with
NULL values again. This means, we now get NULL values for all (former)
pairs which have "mhgd.HostGroupID != 2".
Now the WHERE clause can be applied. "mhgd.HostID IS NULL" now chooses
all records, which have NULL values for the right table, i.e. all
pairs, which had no match on the condition "mh.HostID = mhgd.HostID
AND mhgd.HostGroupID = 2", this means all records of the left table,
for which there was no matching HostID in mhgd which also was in
mhgd.HostGroupID = 2. The latter is a different wording for
... mh.HostID NOT IN ( SELECT mhgd.HostID
FROM monitorhostgroupdetails mhgd
WHERE mhgd.HostGroupID = 2 )
Which should look familiar to you. ;-)
"mh.CompanyID = 1" restricts the result to only the company in
question, of course.
Of course, the RDBMS (here MySQL) optimizes how it retrieves the pairs
you want. But the above is the underlying logic of how it works.
Hope that helped,
Benjamin.
--
[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
