Mismatch Records field need display Null

[imtmub]

I have 3 table for Purchase Order from ERP Database
Tables:
POD=Purchase order Details(this table has Require Qty field)
POR=Purchase Order Receive (This table has RequireQty and ReceiptQty Fields)
POI=Purchase order Inspect

I want a query to display Purchase order History. My Query is here

SELECT dbo.POD.POD_PurchOrderID, dbo.POD.POD_POLineNbr, dbo.POI.POI_StatusCode, 
      dbo.ProjectGroup.PRJG_ProjectID, dbo.POI.POI_Reference, dbo.POI.POI_ItemName, 
      dbo.POI.POI_UnitMeasure, dbo.POD.POD_RequiredDate, dbo.POD.POD_PromiseDate, 
      dbo.POR.POR_ReceiverDate, dbo.POD.POD_RequiredQty, dbo.POR.POR_ReceiptQty, dbo.POD.POD_POUnitPrice, 
      dbo.POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt
FROM dbo.POD INNER JOIN
      dbo.POR ON dbo.POD.POD_RequiredDate = dbo.POR.POR_RequiredDate AND 
      dbo.POD.POD_PurchOrderID = dbo.POR.POR_PurchOrderID INNER JOIN
      dbo.POI ON dbo.POD.POD_POLineNbr = dbo.POI.POI_POLineNbr AND 
      dbo.POR.POR_POLineNbr = dbo.POI.POI_POLineNbr AND 
      dbo.POD.POD_PurchOrderID = dbo.POI.POI_PurchOrderID INNER JOIN
      dbo.ProjectGroup ON 
      dbo.POD.POD_PRJG_RecordID = dbo.ProjectGroup.PRJG_RecordID

This query only displaying the records which i have received and inspect. But still i have some records thats not received and inspect.

I want to display all the records which is received and not recieved
If not received receipt qty field should be null.

ERP generate records according porecipt. If not receipt then no records in POR table

Thnx

[ck9663]

It's because you're using INNER JOIN.

Try using LEFT or RIGHT JOIN.

-- CK

[imtmub]

It's because you're using INNER JOIN.

Try using LEFT or RIGHT JOIN.

-- CK

If i use right join i m getting the same result. but left join producing duplicate records.

Example result:
Now i m getting the report like this(This is Example displaying only few fields)

PurchOrderID,POLineNbr,ProjectID,....,RequiredQty, ReceiptQty ,.........
PO12345, 1, S01243, ....,45.00, 25.00, ..........
PO12345, 1, S01234, .....,10.00, 10.00,...........
PO12345, 2, S01234, .....,25.00, 25.00 ..........

But i want the result like This

PurchOrderID,POLineNbr,ProjectID,....,RequiredQty, ReceiptQty ,.........
PO12345, 1, S01243, ....,45.00, 25.00, ..........
PO12345, 1, S01234, .....,10.00, 10.00,...........
PO12345, 2, S01234, .....,25.00, 25.00 ..........
PO12345, 3, S01233, ......,15.00, 0.00, ...........



In this above example the POLineNbr 3 (result row 4) still not yet received. So i dont records exist POR Table. But i want to display those records in my report.

Please give me the query or example site to get this

Thanks

[ganeshkumar08]

SELECT dbo.POD.POD_PurchOrderID, dbo.POD.POD_POLineNbr, dbo.POI.POI_StatusCode,

dbo.ProjectGroup.PRJG_ProjectID, dbo.POI.POI_Reference, dbo.POI.POI_ItemName,

dbo.POI.POI_UnitMeasure, dbo.POD.POD_RequiredDate, dbo.POD.POD_PromiseDate,

dbo.POR.POR_ReceiverDate, dbo.POD.POD_RequiredQty, dbo.POR.POR_ReceiptQty, dbo.POD.POD_POUnitPrice,

dbo.POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt

FROM dbo.POD
--- In SQL the Table follow FROM clause is considered as LEFT Table
-- The Table which follow the JOIN stmt are considered as RIGHT Table
INNER JOIN

dbo.POR ON dbo.POD.POD_RequiredDate = dbo.POR.POR_RequiredDate AND

dbo.POD.POD_PurchOrderID = dbo.POR.POR_PurchOrderID INNER JOIN

dbo.POI ON dbo.POD.POD_POLineNbr = dbo.POI.POI_POLineNbr AND

dbo.POR.POR_POLineNbr = dbo.POI.POI_POLineNbr AND

dbo.POD.POD_PurchOrderID = dbo.POI.POI_PurchOrderID INNER JOIN

dbo.ProjectGroup ON

dbo.POD.POD_PRJG_RecordID = dbo.ProjectGroup.PRJG_RecordID

-------------------------------------------------------------------------------------------------------
In SQL the Table follow FROM clause is considered as LEFT Table
The Table which follow the JOIN stmt are considered as RIGHT Table

Based on the above information u can try...

Ganesh

[imtmub]

SELECT dbo.POD.POD_PurchOrderID, dbo.POD.POD_POLineNbr, dbo.POI.POI_StatusCode,

dbo.ProjectGroup.PRJG_ProjectID, dbo.POI.POI_Reference, dbo.POI.POI_ItemName,

dbo.POI.POI_UnitMeasure, dbo.POD.POD_RequiredDate, dbo.POD.POD_PromiseDate,

dbo.POR.POR_ReceiverDate, dbo.POD.POD_RequiredQty, dbo.POR.POR_ReceiptQty, dbo.POD.POD_POUnitPrice,

dbo.POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt

FROM dbo.POD
--- In SQL the Table follow FROM clause is considered as LEFT Table
-- The Table which follow the JOIN stmt are considered as RIGHT Table
INNER JOIN

dbo.POR ON dbo.POD.POD_RequiredDate = dbo.POR.POR_RequiredDate AND

dbo.POD.POD_PurchOrderID = dbo.POR.POR_PurchOrderID INNER JOIN

dbo.POI ON dbo.POD.POD_POLineNbr = dbo.POI.POI_POLineNbr AND

dbo.POR.POR_POLineNbr = dbo.POI.POI_POLineNbr AND

dbo.POD.POD_PurchOrderID = dbo.POI.POI_PurchOrderID INNER JOIN

dbo.ProjectGroup ON

dbo.POD.POD_PRJG_RecordID = dbo.ProjectGroup.PRJG_RecordID

-------------------------------------------------------------------------------------------------------
In SQL the Table follow FROM clause is considered as LEFT Table
The Table which follow the JOIN stmt are considered as RIGHT Table

Based on the above information u can try...

Ganesh

Hi Ganesh,
Sorry it has taken me a while to get back to you, but thanks very much for your sql

Imthi

[imtmub]

But Still i didnt get what i want. I have three table (POD-PoDetails, POR-PoRecieve & POI-PoInspect)
POD is base table for the rest. In the POR & POI the records only generated if the Item received/Inspect Respectively. Also both POR&POI operation not done in same time some times they will take upto one week. Untill then the record not exist in the respective table.

My query fields link with all this 3 tables if the record is not exist even in one table the records it wont display. Its basic i know.

But i need the recorsd to display. if the field is not exist then it should show NULL value.

Thanks

[Delerna]

Try this

SELECT     POD.POD_PurchOrderID, 
    POD.POD_POLineNbr, 
    POI.POI_StatusCode, 
          ProjectGroup.PRJG_ProjectID, 
    POI.POI_Reference, 
    POI.POI_ItemName, 
    POI.POI_UnitMeasure, 
    POD.POD_RequiredDate, 
    POD.POD_PromiseDate, 
    POR.POR_ReceiverDate, 
    POD.POD_RequiredQty, 
    POR.POR_ReceiptQty, 
    POD.POD_POUnitPrice, 
    POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt
FROM dbo.POD 
LEFT JOIN POR ON POD.POD_PurchOrderID = POR.POR_PurchOrderID 
LEFT JOIN POI ON POD.POD_PurchOrderID = POI.POI_PurchOrderID 
             AND POR.POR_POLineNbr = POI.POI_POLineNbr 
LEFT JOIN ProjectGroup ON POD.POD_PRJG_RecordID = ProjectGroup.PRJG_RecordID
 

If that dosn't work then post all the field names in all of the involved tables and maybe some demo data along with it and I will give you one that does.
It is not absolutely clear on what the necessary fields to join to are because you only list a few, and some of the crucial ones are missing.

I don't know why you joined to the RequiredDate so I removed it. I felt it might be unnecessary.
Anyway, hopefully the above will work for you. If not post what I requested

[imtmub]

Thnx for ur support. Its Ok now