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