-1

I am developing a TSQL query using SSMS 2008 R2 and I want to optimize this sproc that currently uses many left joins. There is a lookup table that contains one row / lookup value. So my TSQL code looks like this:

Table A = main record table
Table B = Lookup Table
  row1 x unique_identifier, y varchar(100)
  row2 x unique_identifier, y varchar(100)
  row3 x unique_identifier, y varchar(100)
  row4 x unique_identifier, y varchar(100)

so each row in this lookup table has two values: one unique_identifier and the other value is a varchar. And so currently my sproc code looks like:

select *
FROM A
LEFT JOIN B ON B.X = A.X WHERE B.X = 123456
LEFT JOIN B2 ON B2.X = A.X WHERE B2.X = 123457
LEFT JOIN B3 ON B3.X = A.X WHERE B3.X = 123458
LEFT JOIN B4 ON B4.X = A.X WHERE B4.X = 123459
LEFT JOIN B5 ON B5.X = A.X WHERE B5.X = 123451

I'm sure there must be a more efficient way to do this though. And my real sproc code actually joins this lookup table 30 times instead of 5 times. I have also tried using a temp table but that didn't seem to optimize it either. Any recommendations for how to replace all of the LEFT JOINs?

OK, so I am updating this question now. This is my exact query:

SELECT DISTINCT 
    lut.[description] as UDT_Injuries,  
    lut2.[description] as abduction, 
    lut3.[description] as ARREST_PARENT
FROM evolv_cs.dbo.incident_header_x x with (nolock)
LEFT JOIN user_defined_lut_rv lut on lut.user_defined_lut_id = x.UDT_Injuries
LEFT JOIN user_defined_lut_rv lut2 on lut2.user_defined_lut_id = x.ABDUCTION
LEFT JOIN user_defined_lut_rv lut3 on lut3.user_defined_lut_id = x.ARREST_PARENT
WHERE lut.[description] IS NOT NULL OR lut2.[description] IS NOT NULL OR lut3.[description] IS NOT NULL

and the first 10 rows of output for this query:

UDT_Injuries    abduction   ARREST_PARENT
NULL    NULL    Outside of facility
Client  NULL    NULL
Client  NULL    Outside of facility
None    NULL    NULL
None    NULL    Outside of facility
Other adult NULL    NULL
Other adult NULL    Outside of facility
Parent  NULL    NULL
Peer    NULL    NULL
Sibling NULL    NULL

FYI, I looked at the article on PIVOT and UNPIVOT, but I do not want to aggregate any data. Instead I want to translate key id values from incident_header_x into varchar values from user_defined_lut_rv.

Sample data from incident_header_x looks like:

UDT_Injuries    ABDUCTION   ARREST_PARENT
5B84773B-99BF-4EB9-8545-EFC06A35FE29    NULL    NULL
NULL    4F18EDE9-BBBA-4430-9EF4-4E28EAC1E6D4    NULL
5B84773B-99BF-4EB9-8545-EFC06A35FE29    NULL    NULL
NULL    NULL    4F18EDE9-BBBA-4430-9EF4-4E28EAC1E6D4
NULL    NULL    5B84773B-99BF-4EB9-8545-EFC06A35FE29

Does this make sense now what I am trying to achieve? To translate these id values into varchar values from the user_defined_lut_rv lookup table?

salvationishere
  • 3,461
  • 29
  • 104
  • 143
  • It is difficult to discern what you're asking. Is there a problem? Do you have 1 lookup table with 30 rows or 30 lookup tables with 1 row? Your code doesn't look like legal syntax. What is wrong with `select * FROM A LEFT JOIN B ON B.X = A.X WHERE B.X = 123456` (even though 123456 is not a uniqueidentifier)? – Tim Lehner Feb 11 '13 at 18:57
  • Just why are you doing 5 joins on the value (123456)? – paparazzo Feb 11 '13 at 18:59
  • OK so I fixed the above TSQL code. Please don't get lost in the details. The main point is that I have one lookup table that I am joining to. I want a more efficient way of including the varchar values into my main Table A. How do I do this? Like could I use a CTE or some other logic? And yes, "123456" is one uniqueidentifier. So currently there are 30 different uniqueidentifier values it joins to. – salvationishere Feb 11 '13 at 19:10
  • You want to show all possible lookup values with each main record? Please let us know the desired output, and some demo data would be helpful. – Tim Lehner Feb 11 '13 at 19:24
  • Can you look at my updated question please? – salvationishere Feb 11 '13 at 21:19

2 Answers2

1

As the join condition is always the same, it seems you are looking for a pivot: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

If this is not what you are looking for, please provide more details. Example data and expected results would be a great start.

Sebastian Meine
  • 11,260
  • 29
  • 41
1

In short, no - there is no more efficient or different way to do this. Are you having performance issues, or are you finding the the code unwieldy?

Anyway you may wish to do some research on 'the one true lookup table', which this looks similar (not identical) to, to get some ideas of disadvantages and advantages in this design.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • OK thanks. I was hoping there was a more efficient way, but I take your word for it. Thanks anyways. – salvationishere Feb 11 '13 at 23:18
  • By more efficient, do you mean less code required, or faster return of records? You definitely can't boil it down to less code - you need to supply all that logic in that SQL to do what you want. You could if you wish use CROSS APPLY instead of LEFT JOIN but it would probably be inefficient and I don't think you would be saving much code. Worse, when someone reads your code afterwards they might assume you used CROSS APPLY for some other reason and leave it that way even if there is a performance issue. – Nick.Mc Feb 12 '13 at 00:33