使用JOIN NULL值匹配DB中的列名

Question

I am trying to create an "eBay" style category menu where when the user selects an item from a multiselect field it will add a new multiselect with any sub categories and if the sub category contains sub categories add a new multi select with the sub sub categories.

I have this mostly working but I am having trouble with the database side of things. I have a main table called categories that contains two columns CatID and Category. There are three rows in the categories table "Motorcycles", "Powersports" and "Parts & Accessories"

When a user selects Motorcycles it should create a new multi select and bring up Motorcycle Brands. If on the other hand the user selects Powersports it should create a new multi dropdown and list Powersport vehicle types "Dirtbike", "PWC", "Snowmobile" etc. Which upon selecting one of those will bring up a Powersports->Vehicle Type->Brand multiselect.

The problem is I don't know how to reference all of this information properly in the database.

For example: Main Categories(categories)

+-------+---------------------+
| CatID | Category            |
+-------+---------------------+
|     1 | Motorcycles         |
|     3 | Parts & Accessories |
|     2 | Powersports         |
+-------+---------------------+

Motorcycles Sub Category: (motorcycle_brands)

+-------+-------+-------------------------+
| CatID | SubID | SubName                 |
+-------+-------+-------------------------+
|     1 |     1 | American Classic Motors |
|     1 |     2 | American Ironhorse      |
|     1 |     3 | Aprilia                 |
|     1 |     4 | Benelli                 |
|     1 |     5 | Big Dog                 |
+-------+-------+-------------------------+

Power Sports Sub Category: (powersport_categories)

+-------+-------+--------------------------------+----+
| CatID | SubID | SubName                        | ID |
+-------+-------+--------------------------------+----+
|     2 |     1 | ATVs                           |  1 |
|     2 |     2 | Dune Buggies / Sand Rails      |  2 |
|     2 |     3 | Go Karts: High-Performance     |  3 |
|     2 |     4 | Personal Watercraft            |  4 |
|     2 |     5 | Powersport Vehicles Under 50cc |  5 |
+-------+-------+--------------------------------+----+

So if I run the following command:

SELECT * FROM categories C 
LEFT JOIN motorcycle_brands MB ON MB.CatID = C.CatID 
LEFT JOIN powersport_categories PC ON PC.CatID = C.CatID 
WHERE C.CatID = 1 LIMIT 5;

I get this:

+-------+-------------+-------------------+-------+-------+-------------------------+-------+-------+---------+------+
| CatID | Category    | CatDBTable        | CatID | SubID | SubName                 | CatID | SubID | SubName | ID   |
+-------+-------------+-------------------+-------+-------+-------------------------+-------+-------+---------+------+
|     1 | Motorcycles | motorcycle_brands |     1 |     1 | American Classic Motors |  NULL |  NULL | NULL    | NULL |
|     1 | Motorcycles | motorcycle_brands |     1 |     2 | American Ironhorse      |  NULL |  NULL | NULL    | NULL |
|     1 | Motorcycles | motorcycle_brands |     1 |     3 | Aprilia                 |  NULL |  NULL | NULL    | NULL |
|     1 | Motorcycles | motorcycle_brands |     1 |     4 | Benelli                 |  NULL |  NULL | NULL    | NULL |
|     1 | Motorcycles | motorcycle_brands |     1 |     5 | Big Dog                 |  NULL |  NULL | NULL    | NULL |
+-------+-------------+-------------------+-------+-------+-------------------------+-------+-------+---------+------+

Notice all the NULL values at the end. Is there a way to get rid of these null values? It is causing other problems in my code because when it returns this array back to my PHP script the SubID and SubName array fields are blank since it goes with the last set which are NULL. However if I run the same query as above replacing 1 with 2 then I get the values I want back since they are not over written by duplicate column names.

Maybe I am going about this all wrong I was planning on having the same SubID and SubName columns in all of my tables for sub categories but it looks like it may cause me grief. The reason I did this is because otherwise I was having to write a separate query depending on what value was selected I wanted to have basically one query that would do it all essentially for each sub option. Is there any improvements here?

Answer 1

1) Try something like this (you should also check my other point below):

SELECT C.CatID, C.Category, C.CatDBTable, MB.SubID AS MbSubID, MB.SubName AS MbSubName, PC.SubID AS PcSubID, PC.SubName AS PcSubName, PC.ID AS PcID FROM categories C 
LEFT JOIN motorcycle_brands MB ON MB.CatID = C.CatID 
LEFT JOIN powersport_categories PC ON PC.CatID = C.CatID 
WHERE C.CatID = 1 LIMIT 5;

2) Your database structure might be made better and more extensible (if you needed for exemple to add a 4th degree sub) if you used the following approach :

Have only one table name categories which has the following columns :

• ID
• parent_id (nullable)
• depth
• name

you will use it as follows:

• "Motorcycles" which is a main category will have: (ID => 1, parent_id => null, depth => 0, name => Motorcycles)
• "American Classic Motors" which is a sub of "Motorcycles" will have: (ID => 4, parent_id => 1, depth => 1, name => American Classic Motors)
• "ATVs" which is a sub of "American Classic Motors" will have: (ID => 5, parent_id => 4, depth => 2, name => ATVs)