Join to same table 3 times

[SQLScholar]

Hey all,

I have a join that is used in many queries and stored procs. I keep thinking that i must be able to not have 3 joins all to the same table but i cant figure it out.

here is effectively what i have:

select * from table1 t1
LEFT JOIN	 dbo.tblMI_Team_Structure	ORG ON T1.TeamName = ORG.TeamName
				AND ORG.Practice IN ( '*All' )
LEFT JOIN	 dbo.tblMI_Team_Structure	ORG2 ON T1.TeamName = ORG2.TeamNam
				AND T1.Practice= ORG2.Practice
LEFT JOIN	 dbo.tblMI_Team_Structure	ORG3 ON T1.TeamName = ORG3.TeamName										AND org3.Practice	= 'Non-Practice'

Now I cant change the data in the tables - they are what they are.

Whenever we want to pull back data from these tables we use coalesce. Priority goes to org, then org2, then org3.

The joins may join on 1,2 or even all 3 of the joins (all three is unlikely) but we only ever want the data on the first join that finds a record as ordered by above.

This has been bugging me for a while - not because its incredibly important just that its had me thinking that it shouldnt be three joins..... but i cant work out if its possible to make it into one join (and if its quicker).

Any advice much appreciated.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

I have an idea... can you post some sample data so I can test?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

Gmmastros,

I have had an idea also..... after weeks of thinking and occasional googling.

Is this the same idea as you?

select * from table1 t1

OUTER APPLY ( 
  SELECT TOP 1
			*
  FROM		dbo.tblMI_Team_Structure ORG
  WHERE		t1.TeamName = ORG.TeamName
			AND ( t1.Practice = ORG.Practice
				  OR ORG.Practice IN ( '*All', 'Non-Practice' )
				)
  ORDER BY	CASE WHEN Practice = '*ALL' THEN 0
				 WHEN practice <> 'Non-Practice' THEN 1
				 ELSE 2
			END
) ORG

If not do you have something better?

Thanks for your thoughts.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

I was thinking this:

select * 
from   table1 t1
       LEFT JOIN dbo.tblMI_Team_Structure ORG 
         ON  T1.TeamName = ORG.TeamName
         And T1.Practice In (ORG.Practice, '*All', 'Non-Practice')

The code above will only work if there are no NULL's in ORG.Practive. While this code *may* produce the same results, and it's certainly shorter, it may not perform as well. You would need to test this, of course.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom