April 21, 2010 at 6:28 am
Hi,
I have some old sql code that was in the *= format that I converted to the LEFT OUTER JOIN format. However, the original query produced 13,051 rows while the new format only 53. I can't figure out where I went wrong. Can you please help me? Here are the queries:
-----Query 1 - Original - 13,051 rows-------------------
SELECT count(*)
FROM Table1 a,
Table2 r,
Table3 c,
Table4 d
WHERE r.TRN = a.TRN
AND r.AcctNo *= c.AcctNo
ANDr.BillerID *= c.BillerID
AND r.AcctNo *= d.AcctNo
AND a.FllwUpDate < Convert(DateTime,convert(varchar(12),getdate(),101))
AND a.FllwUpStat = 'O'
AND r.Owner IS NOT NULL
AND a.ActivID NOT IN ( SELECT ActivID
FROM Table5
WHERE WrkLstStat = 'U' )
AND a.ActivID NOT IN ( SELECT ActivID
FROM Table6
WHERE WrkLstStat = 'U' )
AND ( a.ActivCode IN ( SELECT ActivCode
FROM Table7
WHERE ActivType = 'Rep' )
OR (substring(r.SVCBranchCode, 1, 3) <> 'USM'
AND substring(r.SVCBranchCode, 1, 3) <> 'USG'
))
AND c.AcctName is NULL
-----End Query 1-------------------
-----Query 2 - New - 53 rows-------------------
SELECT count(*)
FROM Table1 a INNER JOIN Table2 r
ON a.TRN = r.TRN
LEFT OUTER JOIN Table3 c
ON r.AcctNo = c.AcctNo AND r.BillerID = c.BillerID
LEFT OUTER JOIN Table4 d
ON r.AcctNo = d.AcctNo
WHERE a.FllwUpDate < Convert(DateTime,convert(varchar(12),getdate(),101))
AND a.FllwUpStat = 'O'
AND r.Owner IS NOT NULL
AND a.ActivID NOT IN ( SELECT ActivID
FROM Table5
WHERE WrkLstStat = 'U' )
AND a.ActivID NOT IN ( SELECT ActivID
FROM Table6
WHERE WrkLstStat = 'U' )
AND ( a.ActivCode IN ( SELECT ActivCode
FROM Table7
WHERE ActivType = 'Rep' )
OR (substring(r.SVCBranchCode, 1, 3) <> 'USM'
AND substring(r.SVCBranchCode, 1, 3) <> 'USG'
))
AND c.AcctName is NULL
-----End Query 2-------------------
Thanks.
April 21, 2010 at 7:30 am
Try putting the 'AND c.AcctName is NULL' into the ON clause of the join to table3.
Also:
a.FllwUpDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
will be more efficient than:
a.FllwUpDate < Convert(DateTime,convert(varchar(12),getdate(),101))
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply