This is my SQL, but I get “error in your SQL syntax”:
x
SELECT
pp.product_id,
p.shop_id,
p.from_shop_id,
p.purchase_type,
pp.product_receive_qty AS purchase_qty,
pp.purchase_product_cost * pp.product_qty AS total_cost,
pr.product_code
FROM
PurchaseProduct pp,
(SELECT * Product01
UNION
SELECT * Product02) AS pr
JOIN
Purchase p ON pp.purchase_id = p.purchase_id
LEFT JOIN
pr ON pp.product_id = pr.product_id
WHERE
pp.product_id IN (".$product_ids.")
AND `p.purchase_status!=0"`
Advertisement
Answer
You’re missing FROM keywords in the UNION subquery. (SELECT * FROM Product01 UNION SELECT * FROM Product02)
Also, you seem to be trying to declare the subquery as pr
and then use it later, but that’s not how SQL’s syntax works.
SELECT
pp.product_id,
p.shop_id,
p.from_shop_id,
p.purchase_type,
pp.product_receive_qty as purchase_qty,
pp.purchase_product_cost*pp.product_qty as total_cost,
pr.product_code
FROM
PurchaseProduct pp
LEFT JOIN
(
SELECT * FROM Product01
UNION
SELECT * FROM Product02
)
AS pr
ON pp.product_id=pr.product_id
INNER JOIN
Purchase p
ON pp.purchase_id=p.purchase_id
WHERE
pp.product_id in (".$product_ids.")
AND p.purchase_status != 0