Inner Join MYSQL Query Combining 3 tables

Question

How can I combine 3 tables in a INNER JOIN?

The end result I am after is getting a list of CATEGORIES belonging to a PRODUCT - including the CATEGORY'S PARENT ID CATEGORY value (ie: Sneakers and Nike).

The CATEGORIES table and PRODUCTS table are joined in the PRODUCTS & CATEGORIES table. A product can belong to many categories and a category can have many products.

Here's more-or-less the setup I have in my database...

CATEGORIES TABLE:

CAT ID | PARENT ID | CATEGORY
1      | 0         | Sneakers
2      | 1         | Nike
3      | 2         | Jordan

PRODUCTS TABLE:

PROD ID 
1
2
3

PRODUCTS & CATEGORIES TABLE:

CAT ID | PROD ID
1      | 0
1      | 1
2      | 3

I am running these queries and I am getting some results, but at the moment I am running 2 separate queries...

$q1 = "SELECT prodid, GROUP_CONCAT(catid SEPARATOR ' // ') as catid FROM products_categories group by prodid order by prodid";
$result1 = $conn->query($q1);

   if ($result1->num_rows > 0) {
      while($prods = $result1->fetch_assoc()) {
         echo "Product Id:" . $prods["prodid"] . " ––> " . "Categories Id:" . $prods["catid"];
      }
   } else {
      echo "0 results";
   }

$q2 =
"  SELECT `ID`.`category` as `IDName`, `LABEL`.`category` as `LabelName`, `LABEL`.`catid` as `LabelId`
   FROM `categories` as ID 
   INNER JOIN `categories` as LABEL
   ON `ID`.`catid` = `LABEL`.`parentid`";
$result2 = $conn->query($q2);

   if ($result2->num_rows > 0) {
      while($prods = $result2->fetch_assoc()) {
            echo "ID# " . $prods["LabelId"] . " is called: ". $prods["LabelName"] . "<br>";
      }
   } else {
      echo "0 results";
   }
$conn->close();

I have tried adding another INNER JOIN with no luck in the results. The end result I am after would be: PROD ID #0 belongs to Sneakers, Nike, Jordan. Anyone can point me in the right direction?

Thank you so much,

Sergio

UPDATE - 10/11/16

The Query:

$q =
"  SELECT PC.productid as productid,  concat_WS('~',C1.category, C2.category, C3.category) as breadcrumb
   FROM xcart_categories as C1

   INNER JOIN xcart_products_categories as PC 
      ON C1.categoryid = PC.categoryid

   LEFT JOIN xcart_categories as C2
      ON C1.categoryid = C2.parentid
      AND C1.parentid = 0

   LEFT JOIN xcart_categories as C3
      ON C2.categoryid = C3.parentid
      WHERE C1.parentid = 0
   ";

The Fetch:

$result = $conn->query($q);

   if ($result->num_rows > 0) {
      while($prods = $result->fetch_assoc()) {
         echo $prods['productid'] . ' Belongs in these categories: ' . $prods['breadcrumb'] . '<br>';
      }
   } else {
      echo "0 results";
   }

It looks like categories table is a hierarchy. MySQL doesn't directly support hierarchical queries. There are ways to simulate the behavior: which are referenced here: (end pointing) :P — xQbert
I understand that MYSQL doesn't support hierarchical queries, but isn't the INNER JOIN handling that part of the query? Or am I missing the point of the INNER JOIN? — Sergio

xQbert xQbert · Accepted Answer · 2016-09-26T17:08:04

This assumes 3 levels of hierarchy no more and a separate join is needed to "put each record on the same line" so they can be combined into a single value result. I thin you were trying to use Group_concat but I can't see how that's going to work as you don't have a way to walk the hierarchy.

SELECT PC.ProductID,  concat_WS('-',C1.Category, C2.Category, C3.Category) as breadcrumb
FROM categories C1
INNER JOIN ProductsCategories PC 
 on C1.categoryID = PC.CategoryID
LEFT JOIN categories C2
 on c1.CategoryID = C2.ParentID
and C1.parentID = 0
LEFT Join Categories C3
 on C2.CategoryID = C3.ParentID
WHERE C1.ParentID = 0

Working SQL Fiddle example ( this only supports 3 levels deep, but could be altered with added left joins to support a max level but not a undetermined max level..)

I see you're trying to use group concat to bring all the rows for the same product category.productID of 0 to the same line

However as 0 references catID of 1 it would only return "sneakers" on the inner join. You would need to traverse the tree (all of it) somehow, thus the above, or you have to take multiple trips to the db or use some sort of dynamic SQL or method mentioned in link in comments.

This would be fairly simple in SQL Server, Oracle or other Enterprise RDBMS systems, however without recursive queries or engine specific hierarchy queries, this is no easy feat in MySQL on a single trip.

Maybe I'm missing something so it may help to see the actual expected results for your sample data. What is the record set look like that you want back?

Inner Join MYSQL Query Combining 3 tables

1 Answers