Left join on a derived table

Transact-SQL
1

Hi, I have this table
now I want to left join it with PORDERITEMSA table base on column ORDI
and add to the output table all the fields in the first select with the new column from table PORDERITEMSA that called REQDATE2 (because the REQDATE2 is sometimes null and I don't want to miss all the orders)
something like that

PORIT.ORDI = PORDERITEMSA .ORDI

SELECT

P.PARTNAME AS 'Part',
system.dbo.tabula_hebconvert(P.PARTDES) AS 'Description',
PORIT.TQUANT/1000 AS 'Quantity',
system.dbo.tabula_dateconvert(PORIT.REQDATE) AS 'Duedate',
BD.BUDCODE AS 'Budget',
PO.ORDNAME AS 'Order',
S.SUPDES AS 'Supplier',
PORIT.QPRICE AS 'Price',
IIF(POA.TAX = -5, PORIT.QPRICE * PORIT.IEXCHANGE, PORIT.QPRICE * (100.0 + 17) / 100.0) AS 'Total'

FROM

PART P,
PORDERITEMS PORIT,
BUDGETS BD,
PORDERS PO,
SUPPLIERS S,
PORDERSA POA,
TAXES TX

WHERE

PORIT.PART = P.PART
AND
PORIT.ORD = PO.ORD
AND
PORIT.BUDGET = BD.BUDGET
AND
PO.SUP = S.SUP
AND
PO.ORDNAME IN('PO20000808', 'PO20001021', 'PO20001150')
AND
PO.ORD = POA.ORD
AND
POA.TAX = TX.TAX
AND
PORIT.QPRICE > 0

2

You need to change to LEFT JOIN instead of WHERE. INNER JOIN will work ok in the WHERE, but INNER JOIN clauses are clearer. So, something like this:

SELECT

P.PARTNAME AS 'Part',
system.dbo.tabula_hebconvert(P.PARTDES) AS 'Description',
PORIT.TQUANT/1000 AS 'Quantity',
system.dbo.tabula_dateconvert(PORIT.REQDATE) AS 'Duedate',
BD.BUDCODE AS 'Budget',
PO.ORDNAME AS 'Order',
S.SUPDES AS 'Supplier',
PORIT.QPRICE AS 'Price',
IIF(POA.TAX = -5, PORIT.QPRICE * PORIT.IEXCHANGE, PORIT.QPRICE * (100.0 + 17) / 100.0) AS 'Total'

FROM

PART P 
LEFT OUTER JOIN PORDERITEMS PORIT ON 
    PORIT.PART = P.PART AND
    PORIT.QPRICE > 0
INNER JOIN BUDGETS BD ON 
    PORIT.BUDGET = BD.BUDGET
INNER JOIN PORDERS PO ON 
    PORIT.ORD = PO.ORD AND
    PO.ORDNAME IN('PO20000808', 'PO20001021', 'PO20001150')
INNER JOIN SUPPLIERS S ON
    PO.SUP = S.SUP
INNER JOIN PORDERSA POA ON
    PO.ORD = POA.ORD
INNER JOIN TAXES TX ON
    POA.TAX = TX.TAX
3

Hi,
but you missed this PORIT.ORDI = PORDERITEMSA .ORDI (this suppose to be on the left join)

4

Adjust the ON columns as needed to match your tables. The main point is that you need to use the JOIN syntax rather than just listing a string of tables in the FROM clause.

5

what about derived table?
if I remember correctly, I need to take the first table and wrap it with a name and do left join to it.
I just don't remember the syntax.

6 
FROM dbo.earlier_table
LEFT OUTER JOIN (
    SELECT ...
    FROM ...
    ...rest_of_derived_table_query
) AS alias ON alias.col_name = earlier_table.col_name