multiple WHEREclause - 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

multiple WHEREclause

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 20:44:51

[code]
SELECT ContactID, FirstName, LastName, UpDated
FROM Contacts
WHERE (NOT EXISTS
(SELECT ContactID, UpDated
FROM ArchiveContacts
WHERE (ContactID= Contacts.ContactID)))[/code]
Gives me a listing of all Contacts which are not entered into ArchiveContacts, but I wish to do an additional check, which also gives me any contact where ArchiveContacts.Updated <> Contacts.UpDated.

So I want, all Contacts that are not in ArchiveContacts + all Contacts where Updated is different.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 21:03:52

I need to add records to my archive, which do not exist, or which have a date change.
At present I can do it in two steps.


SELECT    ContactID, FirstName, LastName, UpDated
FROM      Contacts
WHERE     (NOT EXISTS
                     (SELECT    ContactID, UpDated
                     FROM       ArchiveContacts
                     WHERE      (ContactID= Contacts.ContactID)))

gives me everyone who is not already entered, and


SELECT    Contacts.ContactID, Contacts.FirstName, Contacts.LastName, Contacts.UpDated
FROM      Contacts INNER JOIN
                     ArchiveContacts ON Contacts.ContactID = ArchiveContacts .ContactID AND Contacts.UpDated <> ArchiveContacts .UpDated

gives me all the records already inserted, but that have changed dates

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 22:44:30

quote:
Originally posted by Mopani

SELECT ContactID, FirstName, LastName, UpDated
FROM Contacts
WHERE (NOT EXISTS
(SELECT ContactID, UpDated
FROM ArchiveContacts
WHERE (ContactID= Contacts.ContactID)))

Gives me a listing of all Contacts which are not entered into ArchiveContacts, but I wish to do an additional check, which also gives me any contact where ArchiveContacts.Updated <> Contacts.UpDated.

So I want, all Contacts that are not in ArchiveContacts + all Contacts where Updated is different.

you can use a LEFT JOIN


SELECT c.*
FROM   Contacts c LEFT JOIN ArchiveContacts a
ON     c.ContactID = a.ContactID
WHERE  a.ContactID IS NULL  -- gives you record not exists in ArchiveContacts 
OR     c.Updated  <> a.Updated

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-26 : 23:18:50

[code]try this one r try khtan suggested query using left join
SELECT ContactID, FirstName, LastName, UpDated
FROM Contacts c
WHERE NOT EXISTS
(SELECT ContactID, UpDated
FROM ArchiveContacts
WHERE ContactID= c.ContactID AND Updated <> c.Updated)
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:05:39

quote:

Originally posted by bklr

try this one r try khtan suggested query using left join
SELECT    ContactID, FirstName, LastName, UpDated
FROM      Contacts c
WHERE     NOT EXISTS
                     (SELECT    ContactID, UpDated
                     FROM       ArchiveContacts
                     WHERE      ContactID= c.ContactID AND Updated  <> c.Updated)

i dont think this will give what op asked for. how will this give records which are in ArchiveContacts but with condition a.Updated <> c.Updated?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:07:50

i think it should be something like:-


SELECT    ContactID, FirstName, LastName, UpDated
FROM      Contacts c
WHERE     EXISTS
                     (SELECT    1
                     FROM       ArchiveContacts
                     WHERE      ContactID= c.ContactID AND Updated  <> c.Updated)
OR NOT EXISTS (SELECT    1
                     FROM       ArchiveContacts
                     WHERE      ContactID= c.ContactID)


another way using left join is

SELECT c.*
FROM   Contacts c LEFT JOIN ArchiveContacts a
ON     c.ContactID = a.ContactID
AND c.Updated  = a.Updated
WHERE  a.ContactID IS NULL

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-27 : 23:35:54

Thanks guys, But I am still trying to get this to work..

(Edit: thought it was working, but it is not.)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 00:30:30

so what is the problem ? What is not working ? We can't read mind

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 00:42:49

you need to explain why you think this is not working. If you could post some data and o/p you're trying to achieve, we might understand your scenario better. see below on how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-28 : 00:58:30

Sorry for creating cofusion.
I posted that it was working... when I used

SELECT    ContactID, FirstName, LastName, UpDated
FROM      Contacts c
WHERE     EXISTS
                     (SELECT    1
                     FROM       ArchiveContacts
                     WHERE      ContactID= c.ContactID AND Updated  <> c.Updated)
OR NOT EXISTS (SELECT    1
                     FROM       ArchiveContacts
                     WHERE      ContactID= c.ContactID)

But it was not working correct, because it kept adding records even after they were added, in other words, making duplicates for same date.

So I edited my comment to say it is not working. Then I tried

SELECT c.*
FROM   Contacts c LEFT JOIN ArchiveContacts a
ON     c.ContactID = a.ContactID
AND c.Updated  = a.Updated
WHERE  a.ContactID IS NULL

and now it seems to be working. I am still doing integrity tests, to make sure I can trust it completely.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-28 : 01:09:50

@visakh16
I have read the link on how to ask questions.
I have not conformed to these requests, since I try to simplicate my questions, which I now know is wrong.
I will abide by that request in future.
Thanks for the help, and for that link.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 01:17:12

you get duplicate records with the LEFT JOIN cause there are more than 1 record in ActiveContacts with the same ContactID. Which record do you want ?

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

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-28 : 02:00:59

I fixed it now. Everything is working as it should.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 12:41:51