subtract functionality - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

subtract functionality

Author

Topic

sqldev
Starting Member

4 Posts

Posted - 2005-10-26 : 10:39:09

Hi,

I just want to know whether there is any subtract or minus keywords to find out the difference between 2 select statements or any other way to find out.Let me know.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 10:41:19

Is this?

Select Columns from FirstTable F where not exists(Select * from SecondTable where keycol=F.keycol)

Madhivanan

Failing to plan is Planning to fail

sqldev
Starting Member

4 Posts

Posted - 2005-10-26 : 12:54:19

Hi,

please tell me what is wrong in the below query.

SELECT * FROM CTS_COUNTY_ZIP
WHERE COUNTY_CODE = 1 NOT EXISTS (select * from cts_event t1, cts_territory t2, cts_territory_zip t3, cts_county_zip t4 where t1.end_date is null and t2.end_date is null and t3.end_date is null and t1.event_id = t2.event_id and t2.territory_id = t3.territory_id and t3.zipcode = t4.zip_code and t1.event_id = 200000)

I get a error saying incorrect syntax near keyword NOT.

Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-26 : 14:22:14

WHERE COUNTY_CODE = 1 AND NOT EXISTS

sqldev
Starting Member

4 Posts

Posted - 2005-10-26 : 15:16:53

Hi,

Thanks for the correction.But still i am not getting the desired results.

My first query returns 4 records.My 2nd query reurns 1 record which is there in the first table.So i want the difference the remaining 3 records in first table.Can this be acheived.

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-26 : 16:20:47

Its called 'No Match'

Select Table1.*
From Table1
Left Join Table2
On someCondition
Where Table2.someKeyCol is null

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

sqldev
Starting Member

4 Posts

Posted - 2005-10-26 : 16:41:18

Hi,

I tried out but thats not working .can u give the query.

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-26 : 16:42:44

show the code you tried... and I'll try to go from there

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-10-26 : 18:36:26

This has a marginal chance of working, given the amount of information you've provided us thus far.

[CODE]SELECT *
FROM CTS_COUNTY_ZIP
AS cz

WHERE cz.COUNTY_CODE = 1
AND NOT EXISTS
(
select *
from cts_event
AS t1

JOIN cts_territory
AS t2
ON t1.event_id = t2.event_id

JOIN cts_territory_zip
AS t3
ON t2.territory_id = t3.territory_id

JOIN cts_county_zip
AS t4
ON t3.zipcode = t4.zip_code

where t1.end_date is null
and t2.end_date is null
and t3.end_date is null
and t1.event_id = 200000
AND cz.zip_code = t4.zip_code
)[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources