NOT 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

SQL Server 2000 Forums

Transact-SQL (2000)

NOT JOIN

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-21 : 15:41:26

I have a table ContactHistory and vTeleminderIN, I want to select records that aren't already in ContactHistory based on OutreachId and ContactDate.
I was wondering if this was possible to not join a table based on two fields:


SELECT 	o.RecallId,
	o.OutreachId,
	ti.CallDate,
	r.PatientProfileId,
	'Teleminder Call',
	o.ContactMethodId,
	cr.ContactResultId,
	getdate(),
	'ImportTeleminder',
	getdate(),
	'ImportTeleminder'
FROM vTeleminderin ti
JOIN Outreach o ON o.OutreachId = ti.OutreachId
JOIN Recall r ON r.RecallId = o.RecallId
LEFT JOIN ContactResult cr ON cr.Name = ti.CallResult
LEFT JOIN ContactHistory ch ON ch.OutreachId = ti.OutreachId
WHERE ch.OutreachId <> ti.OutreachId AND
	ch.ContactDate <>  ti.CallDate

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-21 : 16:01:27

What do you mean by not join? The opposite of a join is to not do a join at all, but I must assume you mean something else? If you are asking if what you have done above is possible then, yes it is, what was your result?

Future guru in the making.

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-21 : 16:22:42

Something like this:

SELECT 	o.RecallId,
	o.OutreachId,
	ti.CallDate,
	r.PatientProfileId,
	'Teleminder Call',
	o.ContactMethodId,
	cr.ContactResultId,
	getdate(),
	'ImportTeleminder',
	getdate(),
	'ImportTeleminder'
FROM vTeleminderin ti
JOIN Outreach o ON o.OutreachId = ti.OutreachId
JOIN Recall r ON r.RecallId = o.RecallId
LEFT JOIN ContactResult cr ON cr.Name = ti.CallResult
NOT JOIN ContactHistory ch ON 
	(ch.OutreachId = ti.OutreachId AND ch.ContactDate = ti.Calldate)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-21 : 16:30:57

ccrespo, you need to provide a data example of what you are trying to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-21 : 16:44:52

quote:

Originally posted by ccrespo

Something like this:

SELECT 	o.RecallId,
	o.OutreachId,
	ti.CallDate,
	r.PatientProfileId,
	'Teleminder Call',
	o.ContactMethodId,
	cr.ContactResultId,
	getdate(),
	'ImportTeleminder',
	getdate(),
	'ImportTeleminder'
FROM vTeleminderin ti
JOIN Outreach o ON o.OutreachId = ti.OutreachId
JOIN Recall r ON r.RecallId = o.RecallId
LEFT JOIN ContactResult cr ON cr.Name = ti.CallResult
NOT JOIN ContactHistory ch ON 
	(ch.OutreachId = ti.OutreachId AND ch.ContactDate = ti.Calldate)

You can't NOT JOIN, you need to join and then filter.

Future guru in the making.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-21 : 16:55:04

use not exists.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-09-24 : 02:59:59

if you have an identity...

You can use something like not in (select identity_field from tbl).

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources