Join Is not working - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Join Is not working

Author

Topic

LarryDC
Starting Member

6 Posts

Posted - 2013-05-09 : 11:26:30

Hello,

I have two simple tables in SQL.

The First table contains 4407 rows of data. I then want to add a second table that contains a colum I need to show budget. When I create the Join and select show all rows from the first table only I get hundreds of thousands of records back getting duplicated. Here is my script

SELECT dbo.AccountSummary.Year, dbo.AccountSummary.[Period ID], dbo.AccountSummary.[Account Number], dbo.AccountSummary.[Account Description],
dbo.AccountSummary.[Credit Amount], dbo.AccountSummary.[Debit Amount], dbo.AccountSummary.[Period Balance],
dbo.AccountSummary.[Account Index]
FROM dbo.AccountSummary LEFT OUTER JOIN
dbo.GL00201 ON dbo.AccountSummary.[Account Index] = dbo.GL00201.ACTINDX
WHERE (dbo.AccountSummary.Year = '2013')

What am I doing wrong?

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-05-09 : 11:39:46

Hey Larry,

Before I get to the original question just a bit of advice...

The cleaner your code the easier it is going to be to troubleshoot... Also, make sure not to use spaces in column names as it complicates things in the long run and is bad programming etiquette. I have rewritten the bit of code in an easier way and I personally recommend something in this format for all your future scripts.


SELECT 
	/*For this example I assumed that you would fix the column names and remove spaces. If not then make sure to make it aSum.[Period ID] etc etc */ 
        aSum.Year
	,aSum.PeriodID
	,aSum.AccountDescription
	,aSum.AccountIndex
	-- Here is where you need to add the joined table's data. 
	
FROM [DatabaseName].[dbo].[AccountSummary] aSum 
LEFT JOIN [DatabaseName].[dbo].GL00201 gl ON aSum.AccountIndex = gl.AccountIndex

where aSum.Year = '2013'

With that being said you are getting a Cartesian product... my first guess would be that the account index is not unique. Can you post the design of the tables and some sample data?

LarryDC
Starting Member

6 Posts

Posted - 2013-05-09 : 11:49:11

Thank you. You are right, I just copied the code from the SQL view. Sorry about that , I will make sure I clean up anything before just copy and paste.

You are also right that the Index Field is not unique. I am trying to create a budget report using Great Plains 2010 Tables and realized that I am not having a Unique Index between the two tables.

I will try to find a third table that relates everything, and then post back here in a clean format. Again, Thank you very much for the response. I am very new to SQL and this really helps.

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-05-09 : 11:54:49

No problem at all... SQL can be hard to wrap your head around at first with joining and how to get things to look how you are wanting it to so no worries. I have around 2 years experience and I still look like a novice compared to a lot of people.

Sounds good. If you need any additional help give another shout out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources