Help converting deprecated joins 1

[SaltyTheFrog]

We are on SQL 2008 but our T-SQL is running at SQL 2000 compatibility level. I'm trying to convert some SQL to be compliant so we can move up the compatibility level. I'm having trouble with this one:

SQL 2000 compatibility level:

select *
From
	Employers e,
	Contact c,
	Location l,
	GroupEmployer ge,
	refAppStatus ras,
	AssociationSubAssociation asa,
	Association a
Where
	c.fkLocation =* l.pkLocation
and	l.fkID = e.Employerid
and	l.fkType = 'e'
and 	isnull(l.PrimaryLocation,0) = 1
and  	isnull(c.PrimaryContact,0)  = 1
and	e.employerid = ge.fkemployer
and	ge.fkAppStatus *= ras.pkAppStatus
and	ge.fkAssociationSubAssociation = asa.pkAssociationSubAssociation
and	asa.fkAssociation = a.pkAssociation
and	asa.EstExperienceYear = '2017'

Notice the "c.fkLocation =* l.pkLocation" and "ge.fkAppStatus *= ras.pkAppStatus" joins. The result set contains 48,392 rows.

This is how I converted it:

select *
From
Employers e
join GroupEmployer ge on e.employerid = ge.fkemployer
join AssociationSubAssociation asa on ge.fkAssociationSubAssociation = asa.pkAssociationSubAssociation
join Association a on asa.fkAssociation = a.pkAssociation
join subassociation sa on sa.pksubassociation = asa.fksubassociation
join Location l on l.fkID = e.Employerid
left join Contact c on c.fkLocation = l.pkLocation
left join refAppStatus ras on ge.fkAppStatus = ras.pkAppStatus
left outer join Accountant acc  on e.fkAccountant = acc.pkAccountant
Where l.fkType = 'e'
and 	isnull(l.PrimaryLocation,0) = 1
and  	isnull(c.PrimaryContact,0)  = 1
and	asa.EstExperienceYear =  '2017'

The result set contains 48,352 rows.

The difference is 40 rows and what separates them is a column called "c.PrimaryContact" and the difference is the first query contains forty extra rows with "c.PrimaryContact" as null. From what I see "c.fkLocation =* l.pkLocation" is a right outer join and is allowing null "c.PrimaryContact" rows even though the where specifiaclly says "and isnull(c.PrimaryContact,0) = 1".

I need the null "c.PrimaryContact" rows to be in the results. Just coding for them to be allow NULL "c.PrimaryContact" rows makes the counts much greater than the forty rows. Can someone see what I am missing? I've tried a number of variations, not shown here, but I can't get the queries to match the results set of the first one.

 

[SgtJarrow]

Try the below code. I found two issues with your attempt to make this code SQL-92 compliant.

The first is with one of the OUTER JOINs. You identified 'c.fkLocation =* l.pkLocation' as a LEFT JOIN and the operator (=*) listed is a RIGHT JOIN operatoer. I prefer to use LEFT JOIN only so I reversed the two columns and that should convert the join to be LEFT JOIN. That could clearly be messing with the results because I believe you are joining the table backwards.

The second is your rewrite of the query includes an additional table (Accountant) not found in the original query. Don't know if that was by mistake or not, but first remove that to see if that fixes the record count issue. It was listed as a LEFT JOIN and should not produce the results you are seeing, but it is best to line everything up before adding neew data points to ensure the new data points are not part of the problem.

I also made a couple other minor tweaks that are simply my personal preference, formatting for ease of reading and moving the additional JOIN filters into the JOIN clause instead of the WHERE clause.

This is typed and not tested. If this doesn't meet your needs, please supply a few sample records from each table so it is possible to see what the code is doing and how it needs to be adjusted to find the result set you are looking for.

SELECT
	e.*,
	ge.*,
	asa.*,
	a.*,
	sa.*,
	l.*,
	c.*,
	ras.*
FROM Employers e
INNER JOIN GroupEmployer ge
	ON e.employerid = ge.fkemployer
INNER JOIN AssociationSubAssociation asa
	ON ge.fkAssociationSubAssociation = asa.pkAssociationSubAssociation
		AND asa.EstExperienceYear = '2017'
INNER JOIN Association a
	ON asa.fkAssociation = a.pkAssociation
INNER JOIN subassociation sa
	ON sa.pksubassociation = asa.fksubassociation
INNER JOIN Location l
	ON l.fkID = e.Employerid
		AND l.fkType = 'e'
		AND ISNULL(l.PrimaryLocation, 0) = 1
LEFT JOIN Contact c
	ON l.pkLocation = c.fkLocation
		AND ISNULL(c.PrimaryContact, 0) = 1
LEFT JOIN refAppStatus ras
	ON ge.fkAppStatus = ras.pkAppStatus;

Robert "Wizard" Johnson III
U.S. Military Vets MC
CSM, CSPO, MCPD, CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[SaltyTheFrog]

That did it and thank you for the explanations.