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 2005 Forums
 Transact-SQL (2005)
 SQL Joins need help.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darkjunky
Starting Member

4 Posts

Posted - 07/24/2012 :  10:45:13  Show Profile  Reply with Quote
Hello,

Please, please I need some help understanding what I'm doing wrong!

The problem I'm having is that my query results are being bloated by 10,000 rows duplicate rows due to the way the I am doing joins.

I'm trying to join 4 tables however those 4 tables have no direct relationship they are connected effectly by a series of 13 link tables.

I don't understand how I can join those 4 tables without bloating out my tables by stringing inner joins together I just end up with exactly the correct information just 10,000s of copy of each record.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2012 :  11:04:54  Show Profile  Reply with Quote
use distinct or group by

thats only guess we can do without seeing or having info on any of your data

if you post some sample data and explain what according to you're duplicates and what you're trying to achieve we might be to able to help you better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

darkjunky
Starting Member

4 Posts

Posted - 07/24/2012 :  11:35:15  Show Profile  Reply with Quote
I mean I guess basically what I am asking is "is it possible to join 2 tables via link tables without having to include them JOIN those link tables to the result set.

I would figure this is something everyone would know how to do if they have used SQL for any lenth of time so?

I am trying to make a demo of the problem to post but it might take me a little while.

Distinct and group does give me the results I want, but the under lying query is still creating 100,000 of records to display only around 1000 unique rows. It just takes forever to run the query and there must be a better way of doing. Annoying MySQL 'just does' this.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2012 :  11:44:28  Show Profile  Reply with Quote
"is it possible to join 2 tables via link tables without having to include them JOIN those link tables to the result set.

it depends on what all you want as result

if its just that you want count of related records you dont need to include all the actual tables as such
but if you do need the individual record details, then definitely they also have to be included in join
the reason why your query runs forever maybe because it doesnt have proper indexes . anyways 100000 is not a huge record count so analysing execution plan should give you an idea of where your bottleneck is


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/24/2012 :  11:50:37  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Of course it is. Here's an example: - Copy and paste it into management studio to play round with it.

DECLARE @Employee TABLE (
	[EmployeeID] INT
	, [EmployeeName] VARCHAR(255)
	)
INSERT @Employee ([EmployeeID], [EmployeeName])
VALUES (1, 'Fred')
     , (2, 'Barney')
	
DECLARE @Departments TABLE (
	[DepartmentID] INT
	, [DepartmentName] VARCHAR(255)
	)
INSERT @Departments ([DepartmentID], [DepartmentName])
VALUES (1, 'Sales')
     , (2, 'Collections')
	
DECLARE @EmployeeDepartments TABLE (
	[EmployeeID] INT
	, [DepartmentID] INT
	)
INSERT @EmployeeDepartments ([EmployeeID], [DepartmentID])
VALUES (1, 1)
     , (2, 2)
     
-- Example Select
SELECT
	e.[EmployeeName]
	, d.[DepartmentName]
FROM
	@Employee AS e 
	JOIN @EmployeeDepartments AS ed ON ed.[EmployeeID] = e.[EmployeeID]
	JOIN @Departments AS d ON d.[DepartmentID] = ed.[DepartmentID]


Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/24/2012 :  11:51:52  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
ah misread your post....

Post your query. Either your relationships are incorrect or your join conditions aren't specific enough.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
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.09 seconds. Powered By: Snitz Forums 2000