sql Left Join after UNION, error in your SQL syntax-CodePudding

Following is SQL, but get error in your SQL syntax:

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"`

CodePudding user response：

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

CodePudding user response：

you are missing the word FROM from both the product selects and you can’t just define a dataset in the the middle of your SQL like that and then reference it later. You need to do something like:

LEFT JOIN (SELECT * Product01 
          UNION 
          SELECT * Product02) AS pr
ON pp.product_id=pr.product_id