Need help with long 3 table link (with select from, where, and's, and group by)

[gordo2dope]

ok im back, been a while since ive been here.  I ran across a little situation with a long query here.  well problem is we are going to be selling full price items and sale items and need to have sepereate pages for them.  there are 3 tables (MAIN CATEGORY, 2nd CATEGORY, and ITEMS FOR SALE).  The fullprice page will show everything, and then the sale page will show only items marked as 'S' for sale.

 

Problem is both pages will be dynamically displayed and i dont know how to get the SALE PAGE to only show links to sub categories for items only on sale and have BOTH full price and sale price tables pull from the same category tables.  I know it sounds like a simple query, but here is the actually query im using. 

 

$query = "SELECT category2.*, mczippo1.* FROM `category2`,mczippo1 WHERE category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id";

 

Its pulling the correct data from items table and sub category2 table.  Whoever I dont know how to relate it to the Category1 table. 

 

Here is a snap show of some sample tables.  I altered the data a little make simple sense.  The thing I'm trying to solve is I dont want it to display a MAIN category 1 icon, if there are no items in a sub category of that that are on sale.

 

 

For example:  if theres

JACKETS (leather, denim)

SHIRTS (long sleeve, short sleeve)

GLOVES (cut, full finger),

HELMETS (half ,full face),

PANTS (denim, leather).

 

If theres no helmets of pants on sale i dont want the icon to show up on the main category page. Cause If there is, then youll have an icon option for Half helmet, or full face helmet.  But if there are no helmets on sale then those pages will be empty. 

 

So Like I said, so far the query i pulling the correct SALE ITEMS AND SUB CATEGORIES, but im not sure how to link the CAT2ID in category 2 table to the Category1 name in category1 table all in the same query (if its even possible)  Or maybe if anybody knows another way.  Heres the whole section of code below that im working with here.

 

[pre]

$query = "SELECT category2.*, mczippo1.* FROM `category2`,mczippo1 WHERE category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id";
$data = mysql_query( $query );

$strSQL = mysql_query($query);
while ($part = mysql_fetch_array($strSQL))
		{

			$cell++;
			$cat1 = $part['category1'];
			$cat1id = $part['cat1id'];
			$cat1n = str_replace("_", " ", $cat1); //Strip underscore
			$capcat1 = ucwords($cat1n); // makes first letter of every word uppercase
			$nscat1 = str_replace(" ", "", $cat1); //Strip space
			$andnscat1 = str_replace("&", "", $nscat1); //Strip space
			$findiscat1 = str_replace("&", "&<br>", $capcat1); //replace & with &<br>

				echo "<td valign='top' align='center'><a href='mcsalesubcategory.php?cat=".$cat1id."'><img src='images/pageall/".$cat1id."icon".$andnscat1.".gif' border='0'><br><font color='#672607'><b>".$findiscat1."</font></a></td></tr>";

		}

[/pre]

 

I took some stuff out to simplify it and make it a little more readable and to the point.

Thanks in advance to anybody who takes a look at this.

[gordo2dope]

i think i got it... so far seems ok, im gonna do some testing before i post as solved....

[gordo2dope]

yeah seems like it works, takes a couple seconds to run the query and display the page, but well see how it is in the end.  this is what i ended up changing the query to.

 

$query = "SELECT category1.category1, category1.cat1id FROM `category1`, category2, mczippo1 WHERE category1.cat1id = category2.cat1id AND category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id";