mysql joins related query

pradeepjain

I have 2 tables like

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

  
$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

Expand|Select|Wrap|Line Numbers

 $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

Expand|Select|Wrap|Line Numbers

 $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

Expand|Select|Wrap|Line Numbers

 $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

Expand|Select|Wrap|Line Numbers

on product_features.product_id=product_subcategory.product_id

you are using

Expand|Select|Wrap|Line Numbers

USING (product_id)

mysql joins related query

re: mysql joins related query

re: mysql joins related query

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about