Joing Too Many Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joing Too Many Tables

Author

Topic

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2015-01-14 : 22:42:52

Hi All. Hopefully I will set this up correctly!

I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.

I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:

SELECT

SDH.BatchID,
('0' +
CONVERT (varchar (10), SDH.WorkerID)) AS VendorID,
convert(varchar,( dateadd(hour,-1,SHIT.EndDate) ),101)
+ ' - ' + SDH.Description
AS Invoice,
GL.GLCode,
SDH.Amount

FROM tblSettlementDeductionsHistory AS SDH
INNER JOIN tblSettlementHistory AS SHIT ON SDH.BatchID = SHIT.BatchID
INNER JOIN tblDeductionItems AS DED ON SDH.DeductionID = SDH.DeductionID
INNER JOIN tblGLAccounts AS GL ON DED.GLAccount_ID = GL.GLAccount_ID

WHERE SDH.BatchID = '[[[ Please choose settlement batch. | SELECT SHIT.BatchID,
SHIT.EndDate
FROM tblSettlementHistory AS SHIT ORDER BY SHIT.EndDate DESC]]]'

There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.

What am I doing wrong?

Thanks,
Daniel

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 12:25:49

Probably you need more ON conditions. Take your query apart and add one join at a time to see where the extra rows show up, Then, add additional ON expressions to eliminate them

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2015-01-15 : 16:13:32

What is that concept called so I can read more about it on line?

Thanks again,
dw

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 16:24:18

you already have three of them:

INNER JOIN tblSettlementHistory AS SHIT ON SDH.BatchID = SHIT.BatchID
INNER JOIN tblDeductionItems AS DED ON SDH.DeductionID = SDH.DeductionID
INNER JOIN tblGLAccounts AS GL ON DED.GLAccount_ID = GL.GLAccount_ID

You probably need at least one more. Depends on your data

here's some good reading:
http://technet.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2015-01-16 : 00:48:21

After monkeying around with the query, I've solved 90% of the problem by reversing the order of the tables. There are still a few "soft spots" that I'm having to work on, but it is functional.

All I did was to literally turn the table orders upside down like this:

FROM
tblDeductionItems AS DI
inner join tblGLAccounts AS GLA ON DI.GLAccount_ID = GLA.GLAccount_ID
inner join tblSettlementDeductionsHistory AS SDH ON DI.DeductionID = SDH.DeductionID
INNER JOIN tblSettlementHistory AS SHIT ON SDH.BatchID = SHIT.BatchID

So apparently the table order in the FROM section will change how your data displays and which data displays.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources