Access query vs SQL query

Question

I am (still for those of you who remember) trying to convert an access loading procedure into a sql stored procedure(2000).

I have an access query that returns a different number of rows to the SQL query i have written. Below is the two queries 1/ The SQL query i have written and 2/ The Access query in its SQL view. The SQL query returns 1695 rows and the Access 490.

What am i missing ???

1/
SELECT      r.PersonNo,r.EpisodeNo, r.EpisodeDate, r.Caseload
FROM        ReferralExtract r
LEFT OUTER JOIN [ContactExtract] h
ON  r.EpisodeNo = h.[Referral Identifier] AND r.PersonNo = h.[Person Identifier]
WHERE (r.ReferralDate between '04/01/2009' and '05/31/2009')  and (h.[Referral Identifier] is null) and (r.caseload like 'G%' OR r.caseload like 'B%' OR r.caseload like 'R%')

2/
SELECT  r.PersonNo, r.EpisodeDate, r.Caseload, r.EpisodeNo
FROM  ReferralExtract r
LEFT JOIN [ContactExtract] h
ON ( r.EpisodeNo = h.[Referral Identifier]) AND (r.PersonNo = h.[Person Identifier])
WHERE (((r.ReferralDate) Between [Enter Start Date] And [Enter End Date]) AND ((r.Caseload) Like "R* or G*" Or (r.Caseload) Like "B*") AND ((h.[Referral Identifier]) Is Null));

Thanks in advance

Swan_E

Answer 1

Notice the following in your Access query:

AND ((r.Caseload) Like "R* or G*"

That means you want column r.Caseload to be like "R* or G*".

In your SQLServer query you don't ask it like this.

Regards,
John

Answer 2

Hi John,

Thanks but its essentially the same thing.

Regards

Swan_e

Answer 3

Can you try running the following query in SQL Server:

SELECT
     r.PersonNo
,    r.EpisodeNo
,    r.EpisodeDate
,    r.Caseload
FROM ReferralExtract r
LEFT OUTER JOIN [ContactExtract] h ON
     r.EpisodeNo = h.[Referral Identifier]
     AND r.PersonNo = h.[Person Identifier]
WHERE
     (r.ReferralDate between '04/01/2009' and '05/31/2009')
     and (h.[Referral Identifier] is null)
     and (
          r.caseload like 'R% or G%'
          OR r.caseload like 'B%'
     )

and see how many rows it returns?

Answer 4

Are the cardinalities the same in the contactExtract table for both SQL Server and Access?  Also, what is the purpose of the LEFT JOIN?  Are you just checking to see if the record in the contactExtract table exists?  If so, in SQL Server it is probably better to use a NOT EXISTS semi-join rather than the LEFT JOIN (anti-join).

EDIT:

Also, in SQL Server you can truncate your code slightly by using a LIKE condition such as:

WHERE r.caseload like '[BGR%]'

hmm.  I am not sure if it will perform as well.  As always, take measurements to verify performance. 

Kent Waldrop

---

Kent Waldrop

Answer 5

Thanks John

I still don;t understand why it makes a difference though, but you've made my day.

Thank you
Swan_e

Answer 6

I think your query has got the wrong WHERE conditions.
If you are searching for rows that start with either a "G", an "R" or a "B" in caseload column, then your first SQL Server query is the correct one:

(r.caseload like 'G%' OR r.caseload like 'B%' OR r.caseload like 'R%')