CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Soner Gönül
Soner Gönül

Reputation: 98750

SQL; How can I use Where Condition for only one column

First of all,

Select Count(*)
From PROSPECT_MASTER //Result:12730 row


Select Count(*)
From YAZ..MARDATA.S_MUSTERI // Result: 1112 row

And this is my query;

SELECT a.ID, a.TURQ_ID, a.UNVAN, a.TIP, a.AKTOR, 
       a.GSM_ALAN, a.GSM_TEL, a.IS_ALAN, a.IS_TEL, 
       a.IS_EXT, a.EV_ALAN, a.EV_TEL, a.ADRES, 
       a.STF_KONTAK, 
       (CASE WHEN A.YAZ_ADRES IS NULL
         THEN (B.IS_ADRES1 +' '+B.IS_ADRES2) 
         ELSE A.YAZ_ADRES END) 
       COLLATE DATABASE_DEFAULT AS YAZ_ADRES , A.SEHIR, A.PS, A.MEMO
FROM PROSPECT_MASTER A, YAZ..MARDATA.S_MUSTERI B
WHERE A.ID IN (B.TC_KIM_NO, B.VERGI_NO) // Result 1070 row

As normally, A.ID IN (B.TC_KIM_NO, B.VERGI_NO) condition is working with on my full query, but I want this condition will works only one column (YAZ_ADRES) because A.ID doesn't has all B.TC_KIM_NO or all B.VERGI_NO

My query should get 12730 row like count of PROSPECT_MASTER table. But I want to run A.ID IN (B.TC_KIM_NO, B.VERGI_NO) condition only for YAZ_ADRES column.

How can I do that ?

Upvotes: 0

Views: 4067

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

It sounds to me like what you need is an outer join?

SELECT a.ID, a.TURQ_ID, a.UNVAN, a.TIP, a.AKTOR, 
       a.GSM_ALAN, a.GSM_TEL, a.IS_ALAN, a.IS_TEL, 
       a.IS_EXT, a.EV_ALAN, a.EV_TEL, a.ADRES, 
       a.STF_KONTAK, 
       (CASE WHEN A.YAZ_ADRES IS NULL
         THEN (B.IS_ADRES1 +' '+B.IS_ADRES2) 
         ELSE A.YAZ_ADRES END) 
       COLLATE DATABASE_DEFAULT AS YAZ_ADRES , A.SEHIR, A.PS, A.MEMO
FROM PROSPECT_MASTER A
LEFT OUTER JOIN YAZ..MARDATA.S_MUSTERI B
ON A.ID IN (B.TC_KIM_NO, B.VERGI_NO) // Result 12730 rows

Upvotes: 2

Related Questions