424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

Mismatch Records field need display Null

100+
P: 112
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

Expand|Select|Wrap|Line Numbers
  1. SELECT dbo.POD.POD_PurchOrderID, dbo.POD.POD_POLineNbr, dbo.POI.POI_StatusCode, 
  2.       dbo.ProjectGroup.PRJG_ProjectID, dbo.POI.POI_Reference, dbo.POI.POI_ItemName, 
  3.       dbo.POI.POI_UnitMeasure, dbo.POD.POD_RequiredDate, dbo.POD.POD_PromiseDate, 
  4.       dbo.POR.POR_ReceiverDate, dbo.POD.POD_RequiredQty, dbo.POR.POR_ReceiptQty, dbo.POD.POD_POUnitPrice, 
  5.       dbo.POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt
  6. FROM dbo.POD INNER JOIN
  7.       dbo.POR ON dbo.POD.POD_RequiredDate = dbo.POR.POR_RequiredDate AND 
  8.       dbo.POD.POD_PurchOrderID = dbo.POR.POR_PurchOrderID INNER JOIN
  9.       dbo.POI ON dbo.POD.POD_POLineNbr = dbo.POI.POI_POLineNbr AND 
  10.       dbo.POR.POR_POLineNbr = dbo.POI.POI_POLineNbr AND 
  11.       dbo.POD.POD_PurchOrderID = dbo.POI.POI_PurchOrderID INNER JOIN
  12.       dbo.ProjectGroup ON 
  13.       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
Apr 22 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
It's because you're using INNER JOIN.

Try using LEFT or RIGHT JOIN.

-- CK
Apr 22 '08 #2

100+
P: 112
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
Apr 22 '08 #3

ganeshkumar08
P: 31
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
Apr 23 '08 #4

100+
P: 112
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
May 12 '08 #5

100+
P: 112
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
May 12 '08 #6

Delerna
Expert 100+
P: 1,134
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT     POD.POD_PurchOrderID, 
  2.     POD.POD_POLineNbr, 
  3.     POI.POI_StatusCode, 
  4.           ProjectGroup.PRJG_ProjectID, 
  5.     POI.POI_Reference, 
  6.     POI.POI_ItemName, 
  7.     POI.POI_UnitMeasure, 
  8.     POD.POD_RequiredDate, 
  9.     POD.POD_PromiseDate, 
  10.     POR.POR_ReceiverDate, 
  11.     POD.POD_RequiredQty, 
  12.     POR.POR_ReceiptQty, 
  13.     POD.POD_POUnitPrice, 
  14.     POD.POD_POUnitPrice * dbo.POR.POR_ReceiptQty AS Amt
  15. FROM dbo.POD 
  16. LEFT JOIN POR ON POD.POD_PurchOrderID = POR.POR_PurchOrderID 
  17. LEFT JOIN POI ON POD.POD_PurchOrderID = POI.POI_PurchOrderID 
  18.              AND POR.POR_POLineNbr = POI.POI_POLineNbr 
  19. LEFT JOIN ProjectGroup ON POD.POD_PRJG_RecordID = ProjectGroup.PRJG_RecordID
  20.  

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
May 12 '08 #7

100+
P: 112
Thnx for ur support. Its Ok now
May 19 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.