a left join table what has multiple joins will result in multiple lines for the joining main table. if you want only one row to join use example with 2 joining id fields: use sl; SELECT l.hzs2_order_no ,l.hzs2_line ,l.hzs2_supplier ,l.hzs2_customer_order ,l.hzs2_customer_order_line ,l.hzs2_qty_ordered ,l.hzs2_qty_supplied ,l.hzs2_status ,l.hzs2_cancel ,l.hzs2_location ,l.hzs2_qty_collected ,l.hzs2_location ,ln.tcn_location_description ,p.tcn_process ,t.hzs10_date FROM SL.dbo.hzs2_supplier_order_ln AS l LEFT JOIN sl.dbo.hzs1_supplier_order_hdr AS h ON l.hzs2_order_no = h.F2_1hzs_order_no LEFT JOIN sl.dbo.tcn_location AS ln ON l.hzs2_location = ln.tcn_location_code LEFT JOIN sl.dbo.tcn_location AS p ON l.hzs2_location = p.tcn_location_code LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY hzs10_order,hzs10_line ORDER BY hzs10_date DESC) AS _row FROM sl.dbo.hzs10_supplier_order_transaction_ln) AS t ON l.hzs2_order_no = t.hzs10_order AND l.hzs2_line = t.hzs10_line AND t._row = 1 WHERE l.hzs2_qty_supplied >= l.hzs2_qty_ordered AND hzs2_qty_collected > = l.hzs2_qty_ordered AND h.F2_1hzs_order_no > 0 AND hzs2_status IN (1,2) AND h.hzs1_status_order IN (1) AND l.hzs2_cancel =

https://www.codeproject.com/Questions/1041064/Left-join-with-only-one-row-from-second-table

Another example for unique row in multiple left join LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY tng_order,tng_line_order ORDER BY tng_date DESC) AS _row FROM sl.dbo.tsg2_gold_book_doc_ln) AS g ON g.tng_order = l.hzs2_order_no AND g.tng_line_order = l.hzs2_line AND g._row = 1 13.03.19 another leftjoinwithone ```sql LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY job_name,step,Step_name,job_database ORDER BY updated DESC) AS _row FROM [SL_h].[dbo].[agentjob_log]) AS l ON a.job_name = l.job_name AND a.step = l.step AND a.Step_name = l.Step_name AND a.job_database = l.job_database ``` 13.03.19

• Home
• Notebook
• Contact us

• Privacy Statement
• 

Copyleft ☠ 2024