Dupe checking Query - 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)

Dupe checking Query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-06-03 : 04:04:41

Hi
I have a dupe checking query using data from two tables that is working quite nicely. The two tables are called Contact1 and Contact2.
they are linked by a field in both called Accountno and they have a 1:1 relationship. So there is one row in contact2 for every row in contact1.

I want to limit my dupe checking query so that it only runs on records that have the value "OK" in a contact2 field called Uimport2 and have the value "38DBPOOL" in the OWNER field in Contact1 .

I am struggling to get my head around the syntax of how to join the tables to do this.
===========================================================

SELECT CONTACT1.CONTACT, CONTACT1.PHONE2 as "Mobile", CONTACT1.ACCOUNTNO as "GoldMineID", CONTACT1.KEY1 as "ClientID",CONTACT1.KEY3 as "Consultant", CONTACT2.UIMPORT1 as "Import Name", CONTACT2.UIMPORT2 as "ImportStatus" FROM CONTACT1 , CONTACT2

WHERE CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO AND CONTACT1.CONTACT In

(SELECT CONTACT FROM CONTACT1 As Tmp GROUP BY CONTACT,PHONE2 HAVING Count(*)>1 And CONTACT = CONTACT1.CONTACT And PHONE2 = CONTACT1.PHONE2)

ORDER BY CONTACT1.CONTACT, CONTACT1.PHONE2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 04:36:29

Add a WHERE in your subselect for this:
value "OK" in a contact2 field called Uimport2

Add an AND condition to the WHERE in your main select for this:
have the value "38DBPOOL" in the OWNER field in Contact1

No, you're never too old to Yak'n'Roll if you're too young to die.