Iman_Davoodian
asked on
Creating a Query to do what minus operator do in SQL SERVER 2000
Suppose we have to Table A(a1,a2) , B(b1,b2)
I need a query that results all the records which exists in Table A and not exists in Table B , THe operation like what Minu Operator does . But as you know , it's not possible to use minu operator in SQL SERVER 2000
I need a query that results all the records which exists in Table A and not exists in Table B , THe operation like what Minu Operator does . But as you know , it's not possible to use minu operator in SQL SERVER 2000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL_SERVER_DBA
SELECT * from TableA a WHERE NOT(A.A1) in (SELECT B.B1 FROM TableB b)
SELECT A1,A2 FROM TABLE1 EXCEPT SELECT B1,B2 FROM TABLE2
Hope this helps
Aash.
Hope this helps
Aash.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
select a.*
from tableA a
left join tableB b
on a.a1 = b.b1
and a.a2 = b.b2
where b.b1 is null
from tableA a
left join tableB b
on a.a1 = b.b1
and a.a2 = b.b2
where b.b1 is null
ASKER
Dear matrix_aashh , the except operator doesn't work in SQL SERVER 2000
ASKER
Dear SQL_SERVER_DBA , the code which you've offered results incorrectly while you we have the same values in the first fields and different values in second fields
ASKER
Dear angelll , thanks for you answer , it seems you answer is correct , but I couldn't undrestand the reason of "where b.b1 is null" . I guess it shouldn't work somewhere , am I right ?
>Dear angelll , thanks for you answer , it seems you answer is correct , but I couldn't undrestand the reason of "where b.b1 is null" . I guess it shouldn't work somewhere , am I right ?
it should work.
just try to run the query without that part, and add b.b1 in the select list, and see the values of b.b1 in regards t the rows you want to have returned...
it should work.
just try to run the query without that part, and add b.b1 in the select list, and see the values of b.b1 in regards t the rows you want to have returned...