mysql joins related query

[pradeepjain]

I have 2 tables like

desc product_subcategory;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| product_id     | int(11)      | NO   | PRI | NULL    | auto_increment | 
| product_name   | varchar(200) | NO   |     | NULL    |                | 
| subcategory_id | int(11)      | NO   | MUL | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+
 
desc product_features;
+--------------+--------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field        | Type                                                                                 | Null | Key | Default | Extra          |
+--------------+--------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id           | int(11)                                                                              | NO   | PRI | NULL    | auto_increment | 
| product_id   | int(11)                                                                              | NO   | MUL | NULL    |                | 
| type         | enum('CDMA','GSM','BOTH')                                                            | YES  |     | NULL    |                | 
| Price        | enum('5000-10000','10000-15000','15000-20000','20000-25000','25000-30000')           | YES  |     | NULL    |                | 
| main_feature | enum('BUSINESS PHONE','SMART PHONE','BASIC PHONE','MULTIMEDIA PHONE','CAMERA PHONE') | YES  |     | NULL    |                | 
| weight       | enum('100-200','200-400','400-800','800-1600')                                       | YES  |     | NULL    |                | 
| pic          | longblob                                                                             | YES  |     | NULL    |                | 
+--------------+--------------------------------------------------------------------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

i need to query 2 tables to get the data out was wondering if there is a way to use joins and do it in a single query !!

here is ma php code

 
$sql="select * from product_subcategory where subcategory_id='$sub_category'";
$result=mysql_query($sql);
$count = mysql_num_rows($result);
$myresult ='';
$myresult .="<div id='change'>";
$myresult .="<table border='1' width='100%'>";
$myresult .="<tr><td><b>Pic</b></td><td><b>Type</b></td><td><b>Price</b></td><td><b>Feature</b></td><td><b>Weight</b></td></tr>";
$myresult .="<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>";
if($count > 0){
while($row =  mysql_fetch_array($result)){
$new = $row['product_id'];
$sql1="select * from product_features where product_id='$new'";
$result1=mysql_query($sql1);
while($row1=mysql_fetch_array($result1)){
$myresult .="<tr>";
$myresult .= "<td><center><img src='picdisplay.php?id=". $row1['product_id'] . "'</img></center><br/><center>".$row['product_name']. "</center></td>";
$myresult .= "<td>" . $row1['type']. "</td>";
$myresult .= "<td>" . $row1['Price']. "</td>";
$myresult .= "<td>" . $row1['main_feature']. "</td>";
$myresult .= "<td>" . $row1['weight']. "</td>";
$myresult .="</tr>";
}
}
}

can i do thing in a single query using joins!!!

i tried something like this

$sql1="select * from product_features join product_subcategory on product_features.product_id=product_subcategory.product_id and product_subcategory.subcategory_id='$sub_category'";

but did not work :(

[code green]

$sql1="select * from product_features 
join product_subcategory on
product_features.product_id=product_subcategory.product_id 
and product_subcategory.subcategory_id='$sub_category'"; 
 

It looks fine to me.
I think in this case I would prefer to write

$sql1="select * from product_subcategory 
LEFT join product_features USING (product_id) 
WHERE product_subcategory.subcategory_id='$sub_category'"; 
 

But this should yield the same result.
What do you mean by 'Doesn't work'?

[pradeepjain]

sorry i did not get back! that worked for me ! one loop was extra so it was repeating the entries ..

hope i am correct on this
instead of this

on product_features.product_id=product_subcategory.product_id 

you are using

USING (product_id)