mysql > 多表联合查询

表 __hikashop_category

category_id         category_name

 

表 __hikashop_product_category

id                            category_id               product_id

 

表 __hikashop_product

id                            product_id                 product_name

 

已知表 __hikashop_category  中的category_id = 13 获得 hikashop_product 中 的 product_name

 

SELECT c.product_name FROM

`#__hikashop_category` as a,

`#__hikashop_product_category` as b,

`#__hikashop_product` as c 

WHERE

b.category_id = a.category_id AND

c.product_id = b.product_id AND

a.category_id = 13;

 

** 对查询结果 进行 分组操作

GROUP BY

 

** 同样的操作使用 INNER JOIN

 

$query = 'SELECT  c.product_id,c.product_name,c.product_msrp,d.file_path,e.price_value FROM `#__hikashop_category` as a ';
$query .= 'INNER JOIN `#__hikashop_product_category` as b ON b.category_id = a.category_id ';
$query .= 'INNER JOIN `#__hikashop_product` as c ON c.product_id = b.product_id ';
$query .= 'INNER JOIN `#__hikashop_file` as d ON d.file_ref_id = c.product_id ';
$query .= 'INNER JOIN `#__hikashop_price` as e ON e.price_product_id = d.file_ref_id ';
$query .= 'WHERE a.category_id = 1';
$query .= ' GROUP BY c.product_id ORDER BY c.product_id DESC';