我有3个表:product
,order_details
,orders
。
我想将它们加入一个查询中,以显示用户购买的所有产品。
这是我的代码:
SELECT products.id AS id,
products.kode AS kode,
products.sub_kode AS sub_kode,
products.nama AS nama,
products.barcode AS barcode,
products.harga AS harga,
IFNULL(products.keterangan_kemasan, ' ') AS keterangan_kemasan,
IFNULL(products.keterangan_ukuran ,' ') AS keterangan_ukuran,
SUM(IFNULL(order_details.jumlah_pesanan_akhir,0)) AS jumlah,
IFNULL(order_details.jumlah_pesanan_akhir,0) AS jumlah_beli,
orders.idmember AS member
FROM products LEFT JOIN (orders,order_details)
ON (orders.id = order_details.idorder
AND order_details.product_kode = products.kode
AND order_details.product_subkode = products.sub_kode)
WHERE products.nama LIKE '%asdu%'
AND IFNULL(orders.idmember, 21376) = 21376
GROUP BY products.kode, products.sub_kode
ORDER BY IFNULL(order_details.jumlah_pesanan_akhir,0) DESC,
products.nama ASC
LIMIT 30;
发生的情况是,当客户不再购买该商品时,该商品将不会显示。同时,我希望它们被显示。
如果我从查询中删除它,
AND IFNULL(orders.idmember, 21376) = 21376
一切都会很好地显示。但是它将显示其他人的订购历史记录。
因此,据我了解,您想要所有看起来像的产品%asdu%
,并且想知道客户21376
是否购买了它们。
用户的ID必须是LEFT JOIN子句的一部分,否则它将被过滤掉。
SELECT products.id AS id,
products.kode AS kode,
products.sub_kode AS sub_kode,
products.nama AS nama,
products.barcode AS barcode,
products.harga AS harga,
IFNULL(products.keterangan_kemasan, ' ') AS keterangan_kemasan,
IFNULL(products.keterangan_ukuran ,' ') AS keterangan_ukuran,
SUM(IFNULL(order_details.jumlah_pesanan_akhir,0)) AS jumlah,
IFNULL(order_details.jumlah_pesanan_akhir,0) AS jumlah_beli,
orders.idmember AS member
FROM products
LEFT JOIN order_details
ON order_details.product_kode = products.kode
AND order_details.product_subkode = products.sub_kode
LEFT JOIN orders
ON orders.id = order_details.idorder
AND orders.idmember = 21376
WHERE products.nama LIKE '%asdu%'
GROUP BY products.kode, products.sub_kode
ORDER BY IFNULL(order_details.jumlah_pesanan_akhir,0) DESC,
products.nama ASC
LIMIT 30;
我已经重写了联接,以清楚地了解联接的内容。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句