Hello,
I hope this isn't too far afield of the list discussions but here goes.
I'm trying to fashion a query against a MySQL database. There are three
tables that are relevant. They are:
listing (ID, OfficeID, CatID, SubID, Visible, Deleted)
category (ID, Name)
subcategory (ID, CatID, Name)
the ID fields are all AUTO_INCREMENT (1, 2, 3, ...). Basically what I want is:
listing.ID, category.Name, subcategory.Name, listing.Visible
I've been messing with it for over two hours and still have not gotten the
thing right. The wrinkle is that listing.SubID may = 0, which has no matching
row in table subcategory. In that case, I'm looking for a NULL in
subcategory.Name. All I've managed to eek out of it is rows where SubID has a
matching subcategory.ID.
SELECT listing.ID, category.Name, subcategory.Name, listing.Visible
FROM listing, category, subcategory
WHERE Deleted="N"
AND OfficeID=1
AND subcategory.ID = listing.SubID
AND category.ID = listing.CatID
I have tried all manner of LEFT, RIGHT, INNER, OUTER, STRAIGHT and NATURAL
joins. 1/2 of them spit out syntax errors or 100s of rows. I have not managed
to get it to handle SubID = 0 which means "None".
I'd be most grateful if someone could weave me a path thru the JOIN syntax
(which I really don't get and the manual is not exactly clear about) and
suggest how I might convince MySQL to spit out what I want.
Michael
---------------------------------------------------------------------
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
