July 31, 2008 at 4:20 am
I have this query but the problem with this is the duplicate records and it takes time to load the data. Is there any good suggestion how to improved this query. Thank you in advance.
SELECT dbo.tblPurchaseRequest.CSPRNO,
dbo.tblPurchaseOrder.CSPONO,
dbo.tblPurchaseOrder.PartDesc,
dbo.tblPurchaseOrder.Currency,
dbo.tblPurchaseOrder.Price,
dbo.tblPurchaseOrder.Quantity,
dbo.tblPurchaseOrder.PODate,
dbo.tblGoodsRecieved.CSGRNO,
dbo.tblGoodsRecieved.GRDate,
dbo.tblGoodsRecieved.PartDesc AS GRPartDesc, dbo.tblGoodsRecieved.TotalAmtDelivered,
dbo.tblInvoice.CSIVNo,
dbo.tblInvoice.PartNo,
dbo.PART.PARTDESC AS IVPartDesc, dbo.tblPurchaseOrder.Department
FROM dbo.PART
RIGHT OUTER JOIN
dbo.tblInvoice ON dbo.PART.PARTNO = dbo.tblInvoice.PartNo
RIGHT OUTER JOIN
dbo.tblPurchaseOrder ON dbo.tblInvoice.PartNo = dbo.tblPurchaseOrder.PartNo AND dbo.tblInvoice.CSPONO = dbo.tblPurchaseOrder.CSPONO AND
dbo.PART.PARTDESC = dbo.tblPurchaseOrder.PartDesc AND dbo.PART.PARTNO = dbo.tblPurchaseOrder.PartNo
LEFT OUTER JOIN
dbo.tblGoodsRecieved ON dbo.tblPurchaseOrder.CSPONO = dbo.tblGoodsRecieved.CSPONO
LEFT OUTER JOIN
dbo.tblPurchaseRequest ON dbo.tblPurchaseOrder.CSPRNO = dbo.tblPurchaseRequest.CSPRNO
July 31, 2008 at 2:56 pm
First thing would be determining where your data is originating. Your query starts with Part when it appears it should start at invoice. (by the right join)
<determine a if you always have an invoice and a PO)
FROM tblInvoice inv
INNER JOIN tblPurchaseOrder po ON inv.partNo = po.partNo
AND inv.cspono = po.cspono
LEFT JOIN Part
L J tblGoodRecieved (is it really misspelled in the database?)
L J tblPurchaseRequest
Sql compiler may or maynot have realized from your left join / right join what two tables are required.
Note: The two required tables information was 'guessed' at from the RIGHT OUTER JOINs. If that is incorrect so is this logic.
BTW: if PART is an inner join instead of an outer join (which makes sense from the query) then set it up accordingly.
July 31, 2008 at 3:16 pm
I am going to make some suggestions and then give you a semi-solution.
1. Don't mix LEFT and RIGHT Joins, nothing good can come of it.
2. Only use OUTER joins when you know there can be nulls in the join column on the right side of the join.
3. Start with whatever you can JOIN using an INNER JOIN and work from that. You can even make this a derived table and join to the results.
4. When posting something like this post the table definitions, sample data, desired outcome, and what you are trying to do. See the links in my signature. They will help you get help.
Here is some code that I *think* will work that moved everything to LEFT JOINS, ideally you would eliminate as many of those as possible and I have to believe it will be or else your DB needs some re-factoring:
[font="Courier New"]SELECT
PR.CSPRNO,
PO.CSPONO,
PO.PartDesc,
PO.Currency,
PO.Price,
PO.Quantity,
PO.PODate,
GR.CSGRNO,
GR.GRDate,
GR.PartDesc AS GRPartDesc,
GR.TotalAmtDelivered,
I.CSIVNo,
I.PartNo,
P.PARTDESC AS IVPartDesc,
PO.Department
FROM
dbo.tblPuchaseOrder PO LEFT JOIN
dbo.tblInvoice I ON
PO.CSPONO = I.CSPONO AND
PO.PartNo = I.PartNo LEFT JOIN
dbo.PART P ON
PO.PartDesc = P.PartDesc AND
PO.PartNo = P.PartNo LEFT JOIN
dbo.tblGoodsRecieved GR ON
PO.CSPONO = GR.CSPONO LEFT JOIN
dbo.tblPurchaseRequest PR ON
PO.CSPRNO = PR.CSPRNO
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply