SQL 2008 Alias Table - Left Join - Not getting Unique record 1

[gmmastros]

Try this:

select epn.EntityID ID,
       Max(BPh.AreaCode+' '+ BPh.Number) as BusPhone,
       Max(BFx.areacode+' '+ BFx.Number) as BusFax
from   EntityPhoneNumbers epn 
       left join PhoneNumbers BPh on
            BPh.PhoneNumberID = epn.PhoneNumberID
            and BPh.TypeID = 71  
       left join PhoneNumbers BFx on
            BFx.PhoneNumberID = epn.PhoneNumberID
            and BFx.TypeID = 74
Group By epn.EntityId

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

 

[gmmastros]

If this works, and you would like me to explain, just let me know. Also, if you are interested, I can show you a another method for returning the same data with less joins, which will perform better.

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

 

[mwarner1]

Wow - it worked! Thank you so much

I never would have thought to do that though. Is this a change in the way the data is handled in 2008 as opposed to 2005? Why do I need a Max now when I didn't before?

Either way - thanks again!

 

[gmmastros]

This is not a change in the way data is handled. It's always been this way.



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

 

[mwarner1]

Yes yes and yes to your comment

[ "If this works, and you would like me to explain, just let me know. Also, if you are interested, I can show you a another method for returning the same data with less joins, which will perform better. "]

 

[gmmastros]

First, the explanation.

The method I describe will always work. Always. Your original method only works under controlled circumstances. If there are (near) duplicate values in your phone number table, then you would get multiple rows in your output.

Below is some sample data to prove the point. You can run this on ANY database engine and get the same results. This includes any version/edition of SQL Server, Access, MySQL, Oracle, etc....

Declare @EntityPhoneNumbers Table(EntityID Int, PhoneNumberId int)
Insert Into @EntityPhoneNumbers(EntityID, PhoneNumberId) Values(1403, 1)

Declare @PhoneNumbers Table(PhoneNumberId Int, TypeId Int, AreaCode VarChar(10), Number VarChar(20))
Insert Into @PhoneNumbers Values(1, 71, '561','555-1234')
Insert Into @PhoneNumbers Values(1, 74, '561','555-3333')
Insert Into @PhoneNumbers Values(1, 74, NULL,NULL)
Insert Into @PhoneNumbers Values(1, 71, NULL,NULL)


select distinct
       epn.EntityID ID,
       BPh.AreaCode+' '+ BPh.Number as BusPhone,
       BFx.areacode+' '+ BFx.Number as BusFax
from   @EntityPhoneNumbers epn 
       left join @PhoneNumbers BPh on
            BPh.PhoneNumberID = epn.PhoneNumberID
            and BPh.TypeID = 71  
       left join @PhoneNumbers BFx on
            BFx.PhoneNumberID = epn.PhoneNumberID
            and BFx.TypeID = 74

Notice the dummy data in the phone number tables. There are 2 rows with typeid = 71, and 2 rows with typeid = 74. This causes 4 rows in the output. Think about it this way, if a person had 2 different fax numbers, your distinct query would show both. By using MAX and group by, you would only return 1 fax number, and it would be the one that is alphabetically greater than the other(s).

There is another method you can use to reduce the number of joins, which should also reduce the execution time. Basically, I'm looking at your query and I notice that you are joining to the same table twice. This is not necessarily bad, but in some cases, it does offer a chance to optimize the query a bit. Looking at the 2 joins, the only difference I see is that one join has TypeId = 71 and the other has TypeID = 74. We can move this criteria in to the select clause which will allow us to remove a join.

Ex:

select epn.EntityID ID,
       Max([!]Case When ph.TypeId = 71 Then [/!]Ph.AreaCode+' '+ Ph.Number [!]End[/!])as BusPhone,
       Max([!]Case When ph.TypeId = 74 Then [/!]Ph.areacode+' '+ Ph.Number [!]End[/!]) as BusFax
from   EntityPhoneNumbers epn 
       left join PhoneNumbers Ph on
            Ph.PhoneNumberID = epn.PhoneNumberID
Group By epn.EntityId

Notice the CASE expression in the select clause. For the first one, if TypeId = 71, then you get the phone number, otherwise you get null. The MAX aggregate ignores NULL, so you end up with the phone number when the type is 71. Likewise for the 74. The best part is, since you are able to reduce a table from the query, it will most certainly perform better. For small-ish tables, you may not notice a difference in execution time, but you probably will for larger tables.

Does this make sense?

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

 

[mwarner1]

What puzzles me is that there is only one record per entity with a #71 and only one with a 74 - to me the MAX function is redundant - there is nothing to compare to, but it's working and I will just take it as it is

I am going to use this last method though instead of mine because I have a whole lot more of these alias tables from Phonenumbers that would need to be made and it just looks way more effecient!

Thanks so much for taking the time to explain and give me an alternate solution - Very Much appreciated!

Michele
I love Tek-Tips!!!