Search the Community
Showing results for tags 'SQL OUTER Join'.
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