hideous join statement

just came across this JOIN stmt

wtf?!

LEFT OUTER JOIN (SELECT DealID, MAX(PersonIDInitiator) PersonIDInitiator, MAX(PersonIDExecutor) PersonIDExecutor, MAX(PersonIDSalesPerson) PersonIDSalesPerson FROM (SELECT DealID, CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator, CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor, CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson FROM dbo.DealRole WHERE RoleID BETWEEN 1 AND 3) dr GROUP BY DealID) Roles ON Roles.DealID = D.DealID

ugh!

can someone translate? why are there selects in the join?

You are dealing with what are called "derived tables". Please take a look at the way I have restructured your query:

LEFT OUTER JOIN
( SELECT DealID,
MAX(PersonIDInitiator) PersonIDInitiator,
MAX(PersonIDExecutor) PersonIDExecutor,
MAX(PersonIDSalesPerson) PersonIDSalesPerson
FROM ( SELECT DealID,
CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator,
CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor,
CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson
FROM dbo.DealRole
WHERE RoleID BETWEEN 1 AND 3
) dr
GROUP BY DealID
) Roles
ON Roles.DealID = D.DealID

There are two different derived tables in your join. Derived tables are select expressions that can be substituted for and used for tables similar to the way that views can be substituted for and used in the place of tables. I colored the inner derived table in green. Note the inner derived table has "dr" for an alias that I colored brown.

The outer derived table includes the inner derived table so even though I have colored the inner derived table in green, it is still part of the outer derived table. Notice that the outer derived table has "Roles" for an alias and is colored blue.

I notieced by your "ugh" response to the derived table that you are initial repulsed by the use. This is not uncommon; however, derived tables are an integral part of many advanced queries and I would certainly recommend that you take time to learn the use.

Also, SQL Server 2005 introduced the use of "Common Table Expressions" -- CTEs. I would suggest that you might also want to give the CTE beasties a look

|||ok, so bearing in mind that the [deal role].[deal role id] appears to be a number incremented each time the person id on a deal is changed, then it would appear the code is wrong

i.e. it is returning the highest personid, rather than the person id of the highest role id

so if person A was id = 5 was the original initiator, but then person B with person id of 2 is then the originator, it would wrongly show A instead of B as 'current/latest initiator'|||Yes, it sounds like that logic problem exists. Are you using SQL 2005 or SQL Server 2000?|||sql2k5 sp2|||This looks like a job for the ROW_NUMBER function; please give a look to ROW_NUMBER() and OVER in books online while I cook up an example with what you have given.|||

Adolf:

Here is an example of code using (1) a derived table and (2) the ROW_NUMBER() function with the OVER, PARTITION BY and ORDER BY syntax:

Code Snippet

declare @.dealRole table
( drId integer,
DealID integer,
roleId integer,
PersonId integer,
eventDt datetime
)
insert into @.dealRole
select 1, 11, 1, 51, '3/4/7' union all
select 2, 11, 2, 57, '3/5/7' union all
select 3, 11, 3, 54, '3/8/7' union all
select 4, 11, 1, 47, '4/3/7' union all
select 5, 11, 2, 51, '4/3/7' union all
select 6, 11, 1, 44, '4/5/7' union all
select 7, 12, 1, 52, '4/8/7' union all
select 8, 11, 3, 55, '4/8/7' union all
select 9, 11, 4, 14, '4/15/7'

select dealId,
RoleId,
PersonId,
eventDt
from ( select dealId,
roleId,
row_number() over
( partition by dealId, roleId
order by eventDt desc, drId desc
) as seQ,
PersonId,
eventDt
from @.dealRole
where roleId between 1 and 3
) a
where seq = 1

/*
dealId RoleId PersonId eventDt
-- -- -- --
11 1 44 2007-04-05 00:00:00.000
11 2 51 2007-04-03 00:00:00.000
11 3 55 2007-04-08 00:00:00.000
12 1 52 2007-04-08 00:00:00.000
*/