Two opposite queries return the same data.

[padinka]

I have a problem. I can't use 'exists' as it is not available in the particular software I am using to write this thing (MS Access). I can get around this problem by using a passthru query and using exists but have two trainees that can't. Why do the two queries below return the same results when one is looking for is null and the other is looking for is not null. (dsSurveyId is primary index on DS_SURVEY table and swCaseId is the primary index on the SW_CASE table)

SELECT SW_CASE.swCaseId
FROM SW_CASE , DS_SURVEY
where SW_CASE.swCaseId *= DS_SURVEY.swCaseId
having DS_SURVEY.dsSurveyId IS NOT NULL

SELECT SW_CASE.swCaseId
FROM SW_CASE , DS_SURVEY
where SW_CASE.swCaseId *= DS_SURVEY.swCaseId
having DS_SURVEY.dsSurveyId IS NULL
Trisha
padinka@yahoo.com

 

[Starg]

Trisha,
The problem you have is that you are doing an outer join (*=) and not an inner join (=).
What your query is saying is that you want all rows from SW_CASE and only those rows that match in DS_SURVEY. As you are only selecting back the swCaseID and using a having clause (having's are applied after the join has be completed) you would see the same results for both selects. Try using an inner join and replace the Having with a Where statement

For Example (hope this formats OK).

Table A Table B
A A
B B
C
D
E E

If you do the following select :
select TableA.Column
from TableA, Table B
where TableA.Column *= TableB.Column
having TableB.Column is not NULL

then the result set would be A,B,C,D,E,F

If you do the following :

select TableA.Column
from TableA, Table B
where TableA.Column = TableB.Column
where TableB.Column is not NULL

then the result set would be A,B,E


Hope that helps (and is corrent

Starg

 

[padinka]

This is true. The problem is that I need the opposite query. I need records from table 1 that do not match table 2. And as I said, I can't use exists since it is not available in ms-sql. The 'find unmatched' microsoft query wizard builds the query the same way I wrote it but sybase returns all the records regardless.
Trisha
padinka@yahoo.com

 

[RayPeck]

Here's a possiblity (not knowing your limitations):

SELECT SW_CASE.swCaseId
FROM SW_CASE
where SW_CASE.swCaseId NOT IN
(select DS_SURVEY.swCaseId
from DS_SURVEY
where SW_CASE.swCaseId = DS_SURVEY.swCaseId)

ray.peck@warnerbros.com