List:General Discussion« Previous MessageNext Message »
From:Mike McLagan Date:February 8 2002 8:35pm
Subject:MySQL SQL question
View as plain text  
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 <mysql-thread98986@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-cyon=bestweb.net@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
MySQL SQL questionMike McLagan12 Feb