I have 3 tables, Category, Subcategory and Stores

What I want to do is list like this

Heading --->Category
Subheading --->Subcategory
Store ----->Store Name

Store------>Store Name

Basically it lists the main selected category with the relevant subcategories and stores assocaited with them within a loop. Now I've got this working using a LEFT Join within my query. The columns counter allows the stores to be displayed in 4 columns. However, each subcategory has one store in it. I want to be able to have stores appear in multiple subcategories. What is the best way of doing this? I was thinking of a lookup table (subcategories_to_stores) but how do I set it up to work with the query? Or is there another better way of doing this?


$result = mysql_query("SELECT s.subcatName, s.store_name, s.thumb_nail FROM subcategory s LEFT JOIN store s s.msubcid = s.subcid WHERE cid='$categorytarget' AND active='1' ORDER BY s.subcatName ASC") or die("SELECT error: " . mysql_error());


echo '<tr>';

$currentCat = '';
while($row = mysql_fetch_array($result)) {
$subcatName = $row['subcatName'];
$merchant_name = $row['store_name'];
$thumb_nail = $row['thumb_nail'];

if ($currentCat != $subcatName) {
//if a new category, write category head
echo "<tr><td class='titlebar_sml' colspan=\"4\">$subcatName</td></tr>";
$currentCat = $subcatName;

if(isset($store_name)) {
echo '<td align = "center" width="25%">';
echo '<A HREF="'.$store_name.'"><img src='.$thumb_nail.' /></A><br />' . '<H4><A HREF="'.$store_name).'">'.$store_name.'</A></H4>';

echo '</td>';

echo '<tr><td align ="center" colspan=\"4\">Sorry but this category is empty.</td></tr>';

echo '</tr><tr>';

echo '</tr></table>';