SQLTeam.com | Weblogs | Forums

Help with changing old colde from =*


#1

Hi All

I hope you can help me, I have a snippet of code that is using an old LEFT JOIN (=*) before upgrading from 2008 to 2014, I need to change this code. I have tried using the code below but something simply isnt working.

I have got a 2 query windows open side by side comparing the statements and I believe I have them joining correctly and using the old =* script I return 5 results but the new one simply returns no results.

I have the old code as

FROM  person, busIdentity, accessGroup, accessGroupProperty, timePeriod
    WHERE person.accessGroup = accessGroup.id
            AND accessGroup.id = accessGroupProperty.accessGroupID
            AND accessGroupProperty.accessPropertyId = 1 -- access property is AccessLevel
            AND accessGroupProperty.content = 9 -- AccessLevel is Administrator
            AND Person.status = 0 -- person is active
            AND busIdentity.id  = (Select bus.id from BusIdentity bus, Context ctx
                                   Where ctx.currentValue = bus.description2 AND ctx.subject = 'HeadOffice'
                                         AND ctx.parameter = 'Store')
            AND person.logon not in (select operatorCode from #Operator_new)
            AND timePeriod.busIdentityId = '110007'
            AND timePeriod.personId =* person.id
            AND timePeriod.actualEndTime IS NULL

And the new code as

FROM person p    
    INNER JOIN accessGroup ag ON p.accessGroup = ag.id
    INNER JOIN accessGroupProperty agp ON ag.id = agp.accessGroupID    
    LEFT JOIN timePeriod tp ON tp.personid = p.id
    INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id
    INNER JOIN Context ctx ON ctx.currentValue = bi.description2 
WHERE
    agp.accessPropertyId = 1 -- access property is AccessLevel
    AND agp.content = 9 -- AccessLevel is Administrator
    AND p.status = 0 -- person is active     
    AND tp.busIdentityId = '110007'
    AND tp.actualEndTime IS NULL
    AND ctx.subject = 'HeadOffice' 
    AND ctx.parameter = 'Store'
    AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)

From my understanding the INNER JOIN should replace the WHERE causes. What have I done wrong? I appriciate your help.

Cheers, Jason


#2

I would suggest not using Alias names - the original code referred to the actual table names (as a prefix for the columns). Whilst I would always use Short Alias names, as you have done, I think in this case not changing the original code might help reduce any Typo-errors.

Personally I would move all the WHERE clause elements to the JOIN, but up to you what makes the most sense, SQL won't care! (although it will need at least one condition in the JOIN ... ON)

AND NOT EXISTS (SELECT 1 FROM #Operator_new opn WHERE p.logon = opn.operatorCode)

is not the same thing as

AND person.logon not in (select operatorCode from #Operator_new)

in the case where there is a NULL value in #Operator_new

You have changed

AND busIdentity.id  = (Select bus.id from BusIdentity bus, Context ctx
                                   Where ctx.currentValue = bus.description2 AND ctx.subject = 'HeadOffice'
                                         AND ctx.parameter = 'Store')

to

INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id

They might be equivalent, but maybe not?

Here's my attempt. I've tried to rearrange as little as possible

FROM  person
/**Original logic
	JOIN busIdentity
		ON busIdentity.id  =
		(
			Select bus.id
			from	BusIdentity AS bus
				JOIN Context AS ctx
					 ON ctx.currentValue = bus.description2 
					AND ctx.subject = 'HeadOffice'
                                         AND ctx.parameter = 'Store'
		)
*/
-- Revised logic:
	JOIN Context AS ctx
		 ON ctx.subject = 'HeadOffice'
		AND ctx.parameter = 'Store'
	JOIN busIdentity
		 ON busIdentity.description2  = ctx.currentValue
--
	JOIN accessGroup
		 ON accessGroup.id = person.accessGroup
	JOIN accessGroupProperty
		 ON accessGroupProperty.accessGroupID = accessGroup.id
		AND accessGroupProperty.accessPropertyId = 1 -- access property is AccessLevel
		AND accessGroupProperty.content = 9 -- AccessLevel is Administrator
	LEFT OUTER JOIN timePeriod
		 ON timePeriod.personId = person.id
		AND timePeriod.busIdentityId = '110007'
		AND timePeriod.actualEndTime IS NULL
    WHERE	Person.status = 0 -- person is active
            AND person.logon not in (select operatorCode from #Operator_new)

#3

Hi Kristen.

Thank you for helping me understand it much better then I did. Your revised code worked a treat so thank you very much.

The only thing which I'm confused about is joining on the "Context" and the "BusIdentity" table with the

INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id

I thought this might have been the problem but I really struggled re-writing this section. I can see that your code you have used the JOIN but joined it to column within the same table,

    JOIN Context AS ctx
         ON ctx.subject = 'HeadOffice'
        AND ctx.parameter = 'Store'

I thought a JOIN was to join two tables not to itself.

Kind Regards
Jason


#4

yeah, its cheating a bit. I have done a JOIN with

JOIN MyTable
    ON 1=1

to force the table to be included. The table might only have one row, or its JOIN condition might be handled elsewhere in the code - e.g. in the WHERE.

In this instance my assumption was that you wanted to JOIN to a single, specific, uniquely-identified, row - which the ON + AND will do.


#5

Thanks for that Kristen, very helpful to know and thanks again, you saved me many more hours of pain.