CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
user2625377
user2625377

Reputation: 13

SQL Joins giving wrong count

I have a query below written in non-ansi and I am converting into ansi for Migration from SQL 2008 to 2012. The problem is the count for below queries are not matching.

 Select  count(*) 
 FROM t1 ,t2 ,t3,t4,t5
 WHERE t3.RequestId = t1.GenRequestId
 AND t1.RequestType = 'xxx'
 AND t4.RequestId =* t3.RequestId
 AND t4.ItemTypeId =* t2.ItemTypeId 
 AND t1.managerid *= t5.managerid 

 Select  count(*)
 FROM t1
 LEFT JOIN t5 ON t1.managerid = t5.managerid,
 t4
 RIGHT JOIN t3 ON t4.RequestId = t3.RequestId
 RIGHT JOIN t2 ON t4.ItemTypeId = t2.ItemTypeId
 WHERE t3.RequestId = t1.GenRequestId
 AND t1.RequestType = 'xxx'

I want same count when using joins. Appreciate your help..!!

Upvotes: 0

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

To approach this, rewrite the original so everything is a left outer join:

Select  count(*) 
 FROM t1 ,t2 ,t3,t4,t5
 WHERE t3.RequestId = t1.GenRequestId
 AND t1.RequestType = 'xxx'
 AND t3.RequestId *= t4.RequestId
 AND t2.ItemTypeId *= t4.ItemTypeId 
 AND t1.managerid *= t5.managerid ;

Then use the ordering suggested by this for the final query (tables on the left hand side need to appear before tables on the right hand side):

 SELECT  count(*)
 FROM t1 INNER JOIN
      t3
      ON t3.RequestId = t1.GenRequestId LEFT OUTER JOIN
      t4
      ON t3.RequestId = t4.RequestId LEFT OUTER JOIN
      t2
      ON t2.ItemTypeId = t4.ItemTypeId LEFT OUTER JOIN
      t5
      ON t1.managerid = t5.managerId;
 WHERE t1.RequestType = 'xxx';

To be honest, though, I find the old style join syntax very difficult to work with. And, I find it really difficult to work with mixtures of left outer join and right outer join. I'd be inclined to go back to the data and rewrite the query from scratch.

Upvotes: 1

Related Questions