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 --------------------------------------------------------------------- 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
