May 5, 2016 at 2:31 pm
Hi all,
Sorry if this question is stupid, but I've struggled with it for a while, and I'm sure I'm doing something stupid.
Here's an example of the problem I have when doing joins. We are building an application that reads from QBooks and writes it into a SQL database where we can report on it. Another function in the application is that it writes some alterations on certain fields back into QBooks.
Hence the before/after tables for the invoice lines to track both the original and the modified version of the invoice.
The app works fine and correctly writes all the data to the db, however, when I try to query against it, I get multiple lines for each invoice line, equal to the number of total lines on the invoice (the invoice has 3 line items, I'll get 9 lines back from my query, 3 of each actual line)
Here is the gist of the db structure:
[font="Courier New"]Table: Invoice
ID int PK autoinc
InvNum int
Company varchar(50)
etc...
Table: InvoiceLineBefore
ID int PK autoinc
InvID int FK - relationship to Invoice.ID
PartNum varchar(50)
Description varchar(50)
Price double
etc...
Table: InvoiceLineAfter
ID int PK autoinc
InvID int FK - relationship to Invoice.ID
PartNum varchar(50)
Description varchar(50)
Price double
etc...
[/font]
And here is the query I'm trying to use:
[font="Courier New"]
SELECT I.id
, I.InvNum
, I.Company
, B.description, B.Price
, A.description, A.Price
FROM Invoice I
LEFT JOIN InvoiceDetailBefore B ON B.InvoiceId=I.id
LEFT JOIN InvoiceDetailAfter A ON A.InvoiceId=I.id
[/font]
I've tried using JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN and all provide the same issue.
Expected Result
[font="Courier New"]
ID INVNUM COMPANY DESCRIPTION PRICE DESCRIPTION PRICE
1 1 ABC inc Widget 1.00 Widget 1.00
2 2 XYC llc THINGY 1.25 Thingy 1.25
3 3 Excelsior peanuts .75 Peanuts .75
3 3 Excelsior popCoRn .95 Popcorn .95
3 3 Excelsior Soda .49 Soda .49
[/font]
Actual Result
[font="Courier New"]
ID INVNUM COMPANY DESCRIPTION PRICE DESCRIPTION PRICE
1 1 ABC inc Widget 1.00 Widget 1.00
2 2 XYC llc THINGY 1.25 Thingy 1.25
3 3 Excelsior peanuts .75 Peanuts .75
3 3 Excelsior popCoRn .95 Popcorn .95
3 3 Excelsior Soda .49 Soda .49
3 3 Excelsior peanuts .75 Peanuts .75
3 3 Excelsior popCoRn .95 Popcorn .95
3 3 Excelsior Soda .49 Soda .49
3 3 Excelsior peanuts .75 Peanuts .75
3 3 Excelsior popCoRn .95 Popcorn .95
3 3 Excelsior Soda .49 Soda .49
[/font]
Where am I failing on this query? I've encountered this in several scenarios in the past and have done wonky stuff to get around it like subselects, etc, but it seems there has to be a better way...
May 5, 2016 at 2:52 pm
The problem is that the join is just on the invoice ID. All three lines have the same invoice ID, so all three rows from the before table match all three rows from the after table. You'd want to add the criterion that whatever uniquely identifies invoice line items (maybe part number?) also has to be the same in both before and after tables.
Cheers!
May 5, 2016 at 2:56 pm
Gosh it is hard to say what is happening or even what you really want as there is not much provided in the way of details. Perhaps using a GROUP BY or even DISTINCT is what you are looking for???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 6, 2016 at 6:56 am
Modifying the second join to:
[font="Courier New"]
Inner JOIN InvoiceDetailAfter A on A.InvoiceId= I.id AND A.TxnLineID = B.TxnLineID
[/font]
Solved the problem. Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply