Loading

join query with default value, if no match

join query with default value, if no match

Ankit Shukla

Ankit Shukla

7y1kSQL

Hello All,

Suppose I have 2 tables:

Table1:

Status_CD | Status

---------------------------

1 | A

2 | B

Table2:

SNo | Status_CD

---------------------------

1 | 1

1 | 2

2 | 1

Need count of Status as per SNo And Output should be this:

SNo | Status | Counts

---------------------------------

1 | A | 1

1 | B | 1

2 | A | 1

2 | B | 0

How can I get this output (Row 4). If there is no matching then gives 0 by default?

Thanks in advance.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
The output you require is bit tricky and can be achieved by below query
CREATE TABLE #S1
(
    STATUS_CD INT,
    STATUS VARCHAR(20)
)

CREATE TABLE #S2
(
    SNO INT,
    STATUS_CD INT
)


INSERT INTO #S1 VALUES (1,'A')
INSERT INTO #S1 VALUES (2,'B')

INSERT INTO #S2 VALUES (1,1)
INSERT INTO #S2 VALUES (1,2)
INSERT INTO #S2 VALUES (2,1)

CREATE TABLE #TEMP
(
    SNo INT,
    Status VARCHAR(20),
    Cnt INT
)


INSERT INTO #TEMP
SELECT S2.SNO,S1.STATUS,0 AS CNT FROM
#S1 S1 , #S2 S2

SELECT SNo,Status,SUM(cnt) cnt FROM (
    SELECT s2.SNO,s1.Status,count(s2.STATUS_CD) AS cnt FROM
    #s1 s1 INNER JOIN #s2 s2 ON s1.STATUS_CD = s2.Status_CD
    GROUP BY s2.SNo,s1.Status
    UNION
    SELECT Sno,Status,cnt FROM #temp temp
) test
GROUP BY Sno,Status
The output is as below
Ankit Shukla7y ago
Thank you Mam. Its work for me.