CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Omran55
Omran55

Reputation: 39

Mapping Null Values based on condition from two tables in SQL Server

Am tying to map two tables to display the data as following:

Table1 "Master Source Table"

╔════╦══════════╦═══════════╦════════════╗
║ ID ║ SourceID ║ ExternaID ║ SourceName ║
╠════╬══════════╬═══════════╬════════════╣
║  1 ║ NULL     ║ NULL      ║ AA         ║
║  2 ║ 4        ║ NULL      ║ BB         ║
║  3 ║ 5        ║ 1         ║ CC         ║
║  4 ║ 5        ║ 3         ║ DD         ║
╚════╩══════════╩═══════════╩════════════╝

Table2 "registered customer info with the two sources ID" 

╔════════╦══════════╦═══════════╗
║ custID ║ sourceID ║ ExternaID ║
╠════════╬══════════╬═══════════╣
║      4 ║        4 ║ NULL      ║
║      5 ║        5 ║ 1         ║
║      6 ║        5 ║ 1         ║
║      7 ║        5 ║ 3         ║
╚════════╩══════════╩═══════════╝

below is the desired output result:

╔═══════════════════════════════════════╦══════════╦═══════════════════╦════════════╗
║ Total No. of Customers Per SourceName ║ SourceID ║ ExternalChannelID ║ SourceName ║
╠═══════════════════════════════════════╬══════════╬═══════════════════╬════════════╣
║                                     0 ║ NULL     ║ NULL              ║ AA         ║
║                                     2 ║ 5        ║ 1                 ║ CC         ║
║                                     1 ║ 5        ║ 3                 ║ DD         ║
║                                     1 ║ 4        ║ NULL              ║ BB         ║
╚═══════════════════════════════════════╩══════════╩═══════════════════╩════════════╝

so here in order to find the SourceName used by the customer, i need to map it with master source table based on two column which is SourceID, ExternalID. example: if the customer has a SourceID=NULL AND ExternalID=NULL then it should check from the master table those two columns and get me the SourceName. how to accomplish that?

Upvotes: 0

Views: 872

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use a correlated subquery:

select *, 
      (select isnull(count(*), 0) from customertable c 
       where (c.sourceid = m.sourceid or (c.sourceid is null and m.sourceid is null)) and  
             (c.externalid = m.externalid or (c.externalid is null and m.externalid is null)) )
from mastertable m

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

If your server supports is not distinct from 

select count(*), t1.SourceName from table1 t1
join table2 t2 on t1.SourceId is not distinct from t2.SourceId
  and t1.ExternalId is not distinct from t2.ExternalId
group by t1.SourceName

or

select count(*), t1.SourceName from table1 t1
join table2 t2 on (t1.SourceId = t2.SourceId
  or (t1.SourceId is null and t2.SourceId is null))
  and (t1.ExternalId = t2.ExternalId
  or (t1.ExternalId is null and t2.ExternalId is null))
group by t1.SourceName

Upvotes: 2

Related Questions