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

[CODE=sql]SELECT dbo.POD.POD_Pur chOrderID, dbo.POD.POD_POL ineNbr, dbo.POI.POI_Sta tusCode,
dbo.ProjectGrou p.PRJG_ProjectI D, dbo.POI.POI_Ref erence, dbo.POI.POI_Ite mName,
dbo.POI.POI_Uni tMeasure, dbo.POD.POD_Req uiredDate, dbo.POD.POD_Pro miseDate,
dbo.POR.POR_Rec eiverDate, dbo.POD.POD_Req uiredQty, dbo.POR.POR_Rec eiptQty, dbo.POD.POD_POU nitPrice,
dbo.POD.POD_POU nitPrice * dbo.POR.POR_Rec eiptQty AS Amt
FROM dbo.POD INNER JOIN
dbo.POR ON dbo.POD.POD_Req uiredDate = dbo.POR.POR_Req uiredDate AND
dbo.POD.POD_Pur chOrderID = dbo.POR.POR_Pur chOrderID INNER JOIN
dbo.POI ON dbo.POD.POD_POL ineNbr = dbo.POI.POI_POL ineNbr AND
dbo.POR.POR_POL ineNbr = dbo.POI.POI_POL ineNbr AND
dbo.POD.POD_Pur chOrderID = dbo.POI.POI_Pur chOrderID INNER JOIN
dbo.ProjectGrou p ON
dbo.POD.POD_PRJ G_RecordID = dbo.ProjectGrou p.PRJG_RecordID[/CODE]
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,PO LineNbr,Project ID,....,Require dQty, 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,PO LineNbr,Project ID,....,Require dQty, 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_Pur chOrderID, dbo.POD.POD_POL ineNbr, dbo.POI.POI_Sta tusCode,

dbo.ProjectGrou p.PRJG_ProjectI D, dbo.POI.POI_Ref erence, dbo.POI.POI_Ite mName,

dbo.POI.POI_Uni tMeasure, dbo.POD.POD_Req uiredDate, dbo.POD.POD_Pro miseDate,

dbo.POR.POR_Rec eiverDate, dbo.POD.POD_Req uiredQty, dbo.POR.POR_Rec eiptQty, dbo.POD.POD_POU nitPrice,

dbo.POD.POD_POU nitPrice * dbo.POR.POR_Rec eiptQty 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_Req uiredDate = dbo.POR.POR_Req uiredDate AND

dbo.POD.POD_Pur chOrderID = dbo.POR.POR_Pur chOrderID INNER JOIN

dbo.POI ON dbo.POD.POD_POL ineNbr = dbo.POI.POI_POL ineNbr AND

dbo.POR.POR_POL ineNbr = dbo.POI.POI_POL ineNbr AND

dbo.POD.POD_Pur chOrderID = dbo.POI.POI_Pur chOrderID INNER JOIN

dbo.ProjectGrou p ON

dbo.POD.POD_PRJ G_RecordID = dbo.ProjectGrou p.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_Pur chOrderID, dbo.POD.POD_POL ineNbr, dbo.POI.POI_Sta tusCode,

dbo.ProjectGrou p.PRJG_ProjectI D, dbo.POI.POI_Ref erence, dbo.POI.POI_Ite mName,

dbo.POI.POI_Uni tMeasure, dbo.POD.POD_Req uiredDate, dbo.POD.POD_Pro miseDate,

dbo.POR.POR_Rec eiverDate, dbo.POD.POD_Req uiredQty, dbo.POR.POR_Rec eiptQty, dbo.POD.POD_POU nitPrice,

dbo.POD.POD_POU nitPrice * dbo.POR.POR_Rec eiptQty 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_Req uiredDate = dbo.POR.POR_Req uiredDate AND

dbo.POD.POD_Pur chOrderID = dbo.POR.POR_Pur chOrderID INNER JOIN

dbo.POI ON dbo.POD.POD_POL ineNbr = dbo.POI.POI_POL ineNbr AND

dbo.POR.POR_POL ineNbr = dbo.POI.POI_POL ineNbr AND

dbo.POD.POD_Pur chOrderID = dbo.POI.POI_Pur chOrderID INNER JOIN

dbo.ProjectGrou p ON

dbo.POD.POD_PRJ G_RecordID = dbo.ProjectGrou p.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
[code=sql]
SELECT POD.POD_PurchOr derID,
POD.POD_POLineN br,
POI.POI_StatusC ode,
ProjectGroup.PR JG_ProjectID,
POI.POI_Referen ce,
POI.POI_ItemNam e,
POI.POI_UnitMea sure,
POD.POD_Require dDate,
POD.POD_Promise Date,
POR.POR_Receive rDate,
POD.POD_Require dQty,
POR.POR_Receipt Qty,
POD.POD_POUnitP rice,
POD.POD_POUnitP rice * dbo.POR.POR_Rec eiptQty AS Amt
FROM dbo.POD
LEFT JOIN POR ON POD.POD_PurchOr derID = POR.POR_PurchOr derID
LEFT JOIN POI ON POD.POD_PurchOr derID = POI.POI_PurchOr derID
AND POR.POR_POLineN br = POI.POI_POLineN br
LEFT JOIN ProjectGroup ON POD.POD_PRJG_Re cordID = ProjectGroup.PR JG_RecordID
[/code]


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