confused joins - 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

confused joins

Author

Topic

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-21 : 17:11:03

I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?

select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x

where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-21 : 17:54:35

Somehow it is only returning results where l.y = n.y as if I were using an inner join statement.

The Yak Village Idiot

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-21 : 20:30:19

When you impose a condition (WHERE clause) on a join you essentially make that join an inner join.

Consider the following code:

DECLARE @TableA TABLE (ID int)
DECLARE @TableB TABLE (ID int)

INSERT @TableA
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6

INSERT @TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 6


SELECT *
FROM @TableA a
INNER JOIN @TableB b
ON a.ID = b.ID


SELECT *
FROM @TableA a
LEFT OUTER JOIN @TableB b
ON a.ID = b.ID


SELECT *
FROM @TableA a
LEFT OUTER JOIN @TableB b
ON a.ID = b.ID
WHERE a.ID = b.ID

Notice how the inner join and the left outer join with a where clause return the same results.

I'm not 100% sure how to solve your problem from what you have described, but you might try moving the "conditions" out of your WHERE clause and put them on the join. for example:

select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x AND l.y = s.y
left outer join @tempf f on l.x = f.x AND l.y = f.y
left outer join kpi..temptablee e on l.x = e.x AND l.y = e.y
left outer join @tempn n on l.x = n.x AND l.y = n.y

Or perhaps, you need to do some GROUPing or a DISTINCT to get what you are after. If you have some sample data and expected results, I'm sure we can help you out.

-Ryan

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-03-22 : 13:37:25

Ryan, thanks for the suggestion, that worked out perfectly. I havent been programming for very long, a little over a month maybe, and I spent all day working on complex solutions and finally saw your recommendation and the problem was solved, so thank you very much. Just before I saw your solution, I may have come up with a unique solution which might have worked so I will reiterate it here in case you have time to look at it and give an opinion. The problem that i was solving was trying to work out a join where there could be two instances of x in column x with two different values in column y. So, my jimmy-rig solution was to essentially do this:

cast(trim(x, 3) + (trim(y, 2)) as int. Then pass that as a condition to the join. I know from a performance standpoint it would have been a terrible waste of resources but I think it would have worked. Any thoughts?

The Yak Village Idiot

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-22 : 18:30:20

Yeah that probaly would killer perforrmance, but it might work.

You might want to play around with derived tables or inline views. Building on my previous example I duplicated some data and then got the distinct values. I'm not sure if will help you in your situation though.

DECLARE @TableA TABLE (ID int)
DECLARE @TableB TABLE (ID int)

INSERT @TableA
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6

INSERT @TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 6

SELECT *
FROM 
(SELECT DISTINCT ID FROM @TableA) a
LEFT OUTER JOIN (SELECT DISTINCT ID FROM @TableB) b
ON a.ID = b.ID

-- vs

SELECT *
FROM @TableA a
LEFT OUTER JOIN @TableB b
ON a.ID = b.ID

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources