If not exists - 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 2005 Forums

Transact-SQL (2005)

If not exists

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-13 : 14:00:19

I have a query and in the result set I want results that are not in a certain table.

select distinct
bb.businessname,
cc.businessid,
cc.clientcontractid,
cc.contractnumber,
(select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid)revenue,
bb.businessid

from clientcontract cc
inner join business bb on cc.businessid = bb.businessid and bb.deletedate is null
inner join contact c1 on cc.businessid = c1.businessid and c1.deletedate is null

where (select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid) is null or (select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid) = 0

The rows I want are in the results it is just that they are mixed in with ones that have a siteregionvenueid and the ones I want are not in a table called "siteregionvenue" The businesses have no siteregionvenueid at all. but I can't figure out how to get them Do I do an IF not exists at the beginning??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-13 : 14:07:09

This?

select		bb.businessname,
		cc.businessid,
		cc.clientcontractid, 
		cc.contractnumber,
		bb.businessid,
		sum(bs1.amount) as revenue
from		clientcontract cc 
inner join	business bb on cc.businessid = bb.businessid and bb.deletedate is null
inner join	contact c1 on cc.businessid = c1.businessid and c1.deletedate is null
left join	billingschedule bs1 on bs1.clientcontractid = cc.clientcontractid
group by	bb.businessname,
		cc.businessid,
		cc.clientcontractid, 
		cc.contractnumber,
		bb.businessid
having		sum(bs1.amount) is null
		or sum(bs1.amount) = 0

Peter Larsson
Helsingborg, Sweden

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-15 : 10:52:09

No I modified the query,but it still doesnt give me what I want.

if not exists (select * from siteregionvenue srv
inner join business b on srv.businessid = b.businessid
where srv.businessid = srv.businessid and srv.deletedate is null)
return
select distinct

bb.businessname,
cc.businessid,
cc.clientcontractid,
cc.contractnumber,
(select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid)revenue,
bb.businessid

from clientcontract cc
inner join business bb on cc.businessid = bb.businessid and bb.deletedate is null

where (select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid) is null or (select sum(amount)A1 from billingschedule bs1 where bs1.clientcontractid = cc.clientcontractid) = 0

What I need to happen is the that the rows returned need to have no record of being in the siteregionvenue table...but some that are returning are in the table, any halp?

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-15 : 12:34:59

I have figured it out...thanks anyways

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 13:16:19

Do you care to share the solution with other people?
Also using two subqueries is not very fast.

Peter Larsson
Helsingborg, Sweden

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-02-16 : 14:57:19

select
bb.businessname,
cc.businessid,
cc.clientcontractid,
cc.contractnumber,
isnull((select sum(amount)A1 from billingschedule bs1
where bs1.clientcontractid = cc.clientcontractid
and bs1.deletedate is null
and ratetypeid = 1),0) revenue,
bb.businessid

from
clientcontract cc
inner join business bb on cc.businessid = bb.businessid and bb.deletedate is null

where isnull((select sum(amount)A1 from billingschedule bs1
where bs1.clientcontractid = cc.clientcontractid
and bs1.deletedate is null
and ratetypeid = 1),0) = 0
and not exists (select * from siteregionvenue srv
where srv.businessid = cc.businessid)
and cc.deletedate is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources