SQL Joins need help. - 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 2005 Forums

Transact-SQL (2005)

SQL Joins need help.

Author

Topic

darkjunky
Starting Member

4 Posts

Posted - 2012-07-24 : 10:45:13

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

52326 Posts

Posted - 2012-07-24 : 11:04:54

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/

darkjunky
Starting Member

4 Posts

Posted - 2012-07-24 : 11:35:15

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 11:44:28

"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/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 11:50:37

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/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-24 : 11:51:52

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources