MySQL JOIN problem

reeksy

I have an item and category module written in PHP.

Here is my category table; it uses the ‘parent ID’ method to determine child categories:

tblcategories:

Field / Type
categoryID bigint(20)
parentID bigint(20)
title varchar(50)

Here is my item table:

tblitems:

Field / Type
itemID bigint(20)
title varchar(50)

Here is my linking table which links items to categories. The itemID points to the tblitems table and the categoryID points to the tblcategories table.

tblitem_category_links:

Field / Type
linkID bigint(20)
itemID bigint(20)
categoryID bigint(20)

My question is; how do I write a single query that selects everything from the category table where the parentID is ‘7’ for example and then joins the items table (via the linking table) to select all items that are in that category?

I have achieved this, but I have been doing so using two queries, which is a bit school-boy and not very useful when it comes to LIMIT clauses.

Here is what I have at the moment:

// Get categories
$qry  = "SELECT * ";	
$qry .= "FROM `tblcategories` ";
$qry .= "WHERE parentID = '7' ";

$result = $this->_db->query($qry);

$rows = $result->fetchAll();

$list = array();

foreach ($rows as $row) {
	$list[] = $row;
}

// Get items
$qry  = "SELECT b.* ";	
$qry .= "FROM `tblitem_category_links` a ";
$qry .= "INNER JOIN `tblitems` b ";
$qry .= "ON a.itemID = b.itemID  ";
$qry .= "WHERE a.categoryID = '7' ";

$result = $this->_db->query($qry);

$rows = $result->fetchAll();

foreach ($rows as $row) {
	$list[] = $row;			    

}

// return the built array
return $list;

cgraz

Here's a shot

SELECT c.categoryID,
   c.parentID,
   c.title,
   i.itemID,
   i.title AS itemtitle 
FROM tblcategories AS c
INNER 
   JOIN tblitem_category_links AS tcl 
      ON tcl.categoryID = c.categoryID 
INNER
   JOIN tblitems AS i 
      ON i.itemID = tcl.itemID 
WHERE c.parentID = 7;

If you want everything from the categories table, regardless of if it has a match in the other tables, then switch INNER JOIN to LEFT join.