Looking for the right join

[Dakkadakka]

I'm at an internship working on a shopping cart, and I want to create a navigation bar that rearranges the product categories based on their popularity. This requires two tables. The first table is customer_categories, which shows all customer purchases made in the past three months. Because it only goes up to three months, it presents the problem that items with zero purchases do not show up, which leads me to the next table: The second table is called products, and it has the entire inventory. I need this to find out exactly what are ALL the major categories. First, this query gives me every major category: $categorylist = "SELECT full_nameFROM productsWHERE sub_level =0AND (purchase_cost =0OR purchase_cost IS NULL)"; This yields every category (note that the undefined line at the bottom is items that still need to be given categories, don't worry about that one):1 - CLEARANCE ACCOUNTB - CHEMICAL & JANITORIALC - ODOR CONTROLD - SKIN CARE & PERSONAL HYGIEE - PAPER & DISPENSERF - MOPS, BROOMS & BRUSHESG - FLOOR & CARPET CAREH - FACILITY MAINTENANCE & SAFEI - STORAGE & MATERIAL HANDLINGJ - Waste ReceptaclesK - BAGS & CAN LINERSL - FOOD & BEVERAGE SERVICEM - REGULAR & SMALL APPLN - FOODSERVICE DISPOSABLEP - ROOM AMENITIES & ACCESSOQ - LINEN & ACCESSORIESUNDEF Item To get item popularity I use the following query on the other table (and the undefined items are most popular, lol)$query = "SELECT SUM(qty), name FROM customer_categories where depth = 2 GROUP BY name ORDER BY SUM(qty) DESC"; UNDEF ItemE - PAPER & DISPENSERK - BAGS & CAN LINERSB - CHEMICAL & JANITORIALQ - LINEN & ACCESSORIESL - FOOD & BEVERAGE SERVICEN - FOODSERVICE DISPOSABLEH - FACILITY MAINTENANCE & SAFEP - ROOM AMENITIES & ACCESSOF - MOPS, BROOMS & BRUSHESC - ODOR CONTROLD - SKIN CARE & PERSONAL HYGIEG - FLOOR & CARPET CAREWhat kind of join statement would let me get the bottom result, but with the categories with zero purchases put on the bottom? I tried this but it didn't work. It still leaves out the zero (null) categories.SELECT products.full_name, SUM( customer_categories.qty ) AS qtyFROM productsLEFT OUTER JOIN customer_categories ON products.full_name = customer_categories.nameWHERE products.sub_level =0AND products.purchase_cost =0AND customer_categories.depth =2GROUP BY products.full_nameORDER BY qty DESC UNDEF Item 381480E - PAPER & DISPENSER 1624K - BAGS & CAN LINERS 829Q - LINEN & ACCESSORIES 598L - FOOD & BEVERAGE SERVICE 228N - FOODSERVICE DISPOSABLE 220H - FACILITY MAINTENANCE & SAFE 158P - ROOM AMENITIES & ACCESSO 70F - MOPS, BROOMS & BRUSHES 43C - ODOR CONTROL 36D - SKIN CARE & PERSONAL HYGIE 19G - FLOOR & CARPET CARE 2

[justsomeguy]

A left or right join will get all records from one table, and whatever records it can match in the other table. Records that don't match have nulls for the fields from the other table. The left join and right join are the same, they just differ in which table to get all records from: a LEFT JOIN bb RIGHT JOIN a Both of those will get all records from a, and whatever records match from b. In the left join the table on the "left" returns all records, and in a right join the table on the "right" returns all records. I believe a full join will return all records from both tables and match whatever it can.

[Dakkadakka]

A left or right join will get all records from one table, and whatever records it can match in the other table. Records that don't match have nulls for the fields from the other table. The left join and right join are the same, they just differ in which table to get all records from: a LEFT JOIN bb RIGHT JOIN a Both of those will get all records from a, and whatever records match from b. In the left join the table on the "left" returns all records, and in a right join the table on the "right" returns all records. I believe a full join will return all records from both tables and match whatever it can.

Left and right made no change, but trying a FULL OUTER JOIN or FULL JOIN yield SQL errors. What would the proper syntax for this be? SELECT products.full_name, SUM( customer_categories.qty ) AS qtyFROM productsFULL OUTER JOIN customer_categories ON products.full_name = customer_categories.nameWHERE products.sub_level =0AND products.purchase_cost =0AND customer_categories.depth =2GROUP BY products.full_nameORDER BY qty DESC

[justsomeguy]

If you're using MySQL, it doesn't support full joins. There are workarounds here: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/