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
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)
1 Like
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
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.
Thanks for that Kristen, very helpful to know and thanks again, you saved me many more hours of pain.