How to eliminate multiple-join to the same table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

How to eliminate multiple-join to the same table?

Author

Topic

Soldats
Starting Member

3 Posts

Posted - 2002-11-22 : 15:07:10

I have a query that join the same table several times.
I "wish" to cut it down to 1.
Is it doable along with performance gain?

For example,
T1 t1_id
row1 11

T2 t2_id type value t1_id
row1 21 a ape 11
row2 22 b bear 11

Original query:
select t1_id, t2a.value, t2b.value
from T1
left join T2 t2a
on t2a.t1_id = T1.t1_id and type = 'a'
left join T2 t2b
on t2b.t1_id = T1.t1_id and type = 'b'

So it returns "11, ape, bear" on a single row.
Is there a way to do it with just one join?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 15:17:09

That's the standard way to do it; you need two joins to two copies of the same table.

If performance is slow, make sure you have proper indexes and if you can, remove the LEFT OUTER JOIN and make it an INNER JOIN.

- Jeff

Soldats
Starting Member

3 Posts

Posted - 2002-11-22 : 15:41:21

quote:

That's the standard way to do it; you need two joins to two copies of the same table.

If performance is slow, make sure you have proper indexes and if you can, remove the LEFT OUTER JOIN and make it an INNER JOIN.

- Jeff

Well, I try replacing left join with inner join, the Estimated Execution Plan tells me it's 25% (with left join) vs 75% (with inner join) cost.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 16:09:00

Well, I guess ignore that advice then !

I just looked at your needs a little more carefully and thought of something:



Select t1_Id, 
  MAX(CASE WHEN type = 'a' then Value ELSE NULL END) as ValueA,
  MAX(CASE WHEN type = 'b' then Value ELSE NULL END) as ValueB
FROM
   T2
GROUP BY t1_id

Then, you can just join the above to table1 (if you need to) and you are only doing it once, and you only get one row per t1_ID value.

- Jeff

Edited by - jsmith8858 on 11/22/2002 16:10:35

Soldats
Starting Member

3 Posts

Posted - 2002-11-22 : 17:33:46

quote:

Well, I guess ignore that advice then !

I just looked at your needs a little more carefully and thought of something:

Select t1_Id,
MAX(CASE WHEN type = 'a' then Value ELSE NULL END) as ValueA,
MAX(CASE WHEN type = 'b' then Value ELSE NULL END) as ValueB
FROM
T2
GROUP BY t1_id

Then, you can just join the above to table1 (if you need to) and you are only doing it once, and you only get one row per t1_ID value.

- Jeff

Edited by - jsmith8858 on 11/22/2002 16:10:35

Someone gave similar advice, I'll give it a try.
I'm a bit confused about the max(case... part, but I'll figure it out.
Thanks.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-22 : 22:06:50

The MAX(CASE ...... END) thing is because the query uses a group by to A) eliminate duplicates, and B) eliminate nulls. The case expression isn't used in the group by, so it has to be used inside an aggregate function, or otherwise have an aggregate function involved. MAX and MIN are the only two aggregate functions that will return a character string. They are also handy in removing nulls and keeping just the strings. The MAX(CASE ..... END) construct is also extremely handy when you need to do a cross-tab-like query.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources