NOT Statement in Join - 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

General SQL Server Forums

New to SQL Server Programming

NOT Statement in Join

Author

Topic

matthew230
Starting Member

3 Posts

Posted - 2009-05-25 : 22:18:19

Hi,

I'm new to SQL, I need to get a list of customers who have not had an order (or in this case 'job') within a date period, and omit all the ones who have.

This is what I have so far, but it doesn't work.

select distinct customer.cus_id from customer
join jobs on jobs.cus_id=customer.cus_id where (not jobs.date between 76095 and 999999)
order by jobs.cus_id

Any help greatly appreciated.

Matt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 22:22:46

[code]where ~~not~~ jobs.date not between 76095 and 999999 [/code]

KH
[spoiler]Time is always against us[/spoiler]

matthew230
Starting Member

3 Posts

Posted - 2009-05-25 : 22:41:07

Hi,

I tried moving the 'Not' around, it still brings back everything.

Basically the jobs table can contain hundreds of rows for a single client, what I am trying to extract is within a given date band, the customers from customers table who have no entry in the jobs table.

Hope this makes more sense.

Matt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 22:46:05

[code]
select *
from customer c
where not exists
(
select *
from jobs j
where j.cus_id = c.cus_id
and j.date between 76095 and 999999
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

kishore_pen
Starting Member

49 Posts

Posted - 2009-05-26 : 00:17:07

use convert function in your query instead of number.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 00:22:26