SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join Is not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LarryDC
Starting Member

USA
6 Posts

Posted - 05/09/2013 :  11:26:30  Show Profile  Reply with Quote
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

USA
59 Posts

Posted - 05/09/2013 :  11:39:46  Show Profile  Reply with Quote
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?
Go to Top of Page

LarryDC
Starting Member

USA
6 Posts

Posted - 05/09/2013 :  11:49:11  Show Profile  Reply with Quote
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.
Go to Top of Page

cstokes91
Yak Posting Veteran

USA
59 Posts

Posted - 05/09/2013 :  11:54:49  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000