List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:June 7 2002 3:15am
Subject:Re: sub-queries
View as plain text  
All sorted, thanks a million to Kevin Fries

For archives purpose and anyone else that ever want to do anything like
this... The query I was looking for apparently (Tested and working so far -
I will test it a bit more later when I have more data in the tables):

select monitorhosts.HostID, monitorhosts.HostDescription
   from monitorhosts left join monitorhostgroupdetails on
  monitorhosts.HostID = monitorhostgroupdetails.HostID
  AND monitorhostgroupdetails.HostGroupID = 2
 where monitorhostgroupdetails.HostID is null AND
monitorhosts.CompanyID='1';

Once again, my utmost thanks to Kevin for the solution....   This officially
closes three days of headaches and sleepless nights... :-)

> 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
valid reasons for trying to expose as little as possible over the structures
of the tables and the data that they contain.  Perhaps a little greedy of me
seeing that I am the one seeking the help here, but it paid off at long
last.  I'll try be more specific in future should any *serious* like problem
come up in the future again.

> You already got some quite good pointer where to look at (LEFT JOIN,
> IS NULL, ...) and probably you just have to do some digging to figure
> out the correct query.

I also have to admit... It was pointed out to me about two days ago that I
would need to execute this query with a LEFT JOIN.  Coming from a MSSQL
background, and being used to simply using sub-queries, I did go and read
the section in the manual about LEFT / RIGHT JOIN statements.  After reading
about three times a day, and still now after getting a query that works, I
have to sadly admit, I understand absolutely 0% as to *why* this is working,
or how it is working.  At this stage, all that is importaint to me is that I
got a solution to my direct problem - which is what I wanted.

If anyone feel like taking 10 minutes of their time, and explaining 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).

> Or else, write a more complete example (some sample data + result in a
> table form), which shows what you want. This prevents most
> ambiguities. The examples you provided earlier were hard to read
> (e.g. no column names) and provided to few rows to be unambigous. Or
> provide a sub-select that would do what you want.

Yes, and I'll admit that as a fault on my side.  Table structures and some
data was removed from the tables due to security concirns on my side.  The
data and structures removed was not relavent to the specific query however
(tables / columns against which the query should be made), but I'll have to
agree it did add to the confusion.

> Just for completeness. The sub-query from your first post:
>
> SELECT monitorhosts.HostID
> FROM   monitorhosts
> WHERE  monitorhosts.HostID
>        NOT IN ( SELECT HostID
>                 FROM   monitorhostgroupdetails
> WHERE  monitorhostgroupdetails.HostGroupID='1' )
>
> is equivalent to
>
> SELECT mh.HostID
> FROM   monitorhosts
>        LEFT JOIN monitorhostsgroupdetails mhgd
>        ON mhgd.HostID = mh.HostID AND
>           mhgd.HostGroupID = 1
> WHERE  mhgd.HostID IS NULL
>
> (only adding your 1) to Kevin's query)

Yes, I see and understand that now as well.  The original sub-query posted
by me was incorrect.  The query was originally posted to me via another
member of the list, and seeing MySQL doesn't support sub-queries at all (I
thought 3.23.49 might have had support), I was never able to test the query
to confirm if it is working or not.

However, a solution has been provided, it is working, and I am happy.
Thanks to EVERYONE on here, as well as on the PHP-General mailing list for
helping and trying to sort this out for me.  I really do appreciate all the
support, time and effort all of you guys put into this list.

--
Chris




Thread
sub-queriesChris Knipe6 Jun
  • Re: sub-queriesKiss D├íniel6 Jun
    • Re: sub-queriesChris Knipe6 Jun
      • RE: sub-queriesCal Evans6 Jun
  • Re: sub-queriesSabine Richter6 Jun
  • Re: sub-queriesChris Knipe6 Jun
  • Re: sub-queriesChris Knipe6 Jun
    • RE: sub-queriesKevin Fries7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun
      • Re: sub-queriesChris Knipe7 Jun
        • Re: sub-queriesBenjamin Pflugmann7 Jun