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
SELECT listing.ID, category.Name, subcategory.Name, listing.Visible
FROM listing, category, subcategory
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.
|• MySQL SQL question||Mike McLagan||8 Feb|