How to do an outer join with count based column?

Question

Hi, What can be an efficient way for the following problem in SQL 2008?

First two are input tables, using which I need to populate the 3rd(DataOut table)

Basically, WDATA will have zero or more rows corresponding to each row of DataIn table. I need to populate DataOut table with all the rows, including none matched and multiple matched and populate a status column differentiating between single corresponding row in WDATA, No row in WDATA, or exactly one row in WDATA.

DataIn
QID  RID  DOB
-------------
1    1    01/01/1980
1    2    03/01/1981
1    3    01/02/1991



WDATA(key is QID, RID,PID)
QID  RID  PID
---------------
1    1    101
1    1    102
1    3    204



DataOut
QID  RID  PID  status
-----------------------
1    1    101  ”multiple match”
1    1    102  ”multiple match”
1    2    null ”no match”
1    3    204  ”single match”

Answer 1

How about this query here??

SELECT
    di.QID, di.RID, w.PID,
    CASE (SELECT COUNT(*) FROM WDATA w2 WHERE di.QID = w2.QID AND di.RID = w2.RID)
        WHEN 0 THEN 'no match'
        WHEN 1 THEN 'single match'
        ELSE 'multiple match'
    END AS 'Status'
FROM
    DataIn di 
LEFT OUTER JOIN 
    WDATA w ON di.QID = w.QID AND di.RID = w.RID

For me, it produces this output:

QID  RID  PID   Status
 1    1   101   multiple match
 1    1   102   multiple match
 1    2   NULL  no match
 1    3   204   single match

Is that what you're looking for??

Answer 2

Try this:

SELECT di.QID,di.RID,wd.PID, 
CASE 
   WHEN  wd.PID is null THEN 'no match'
   WHEN COUNT(di.QID) = 1 THEN 'single match'
   WHEN COUNT(di.QID) > 1 THEN 'multiple match'
END
FROM DataIn as di
LEFT JOIN WDATA as wd
ON di.QID = wd.QID AND di.RID = wd.RID
GROUP BY di.QID,di.RID,wd.PID

Answer 3

This looks OK, I think it can be improved on though.

DECLARE @DataIn TABLE
(
    QID INT NOT NULL,
    RID INT NOT NULL,
    DOB DATE NOT NULL
)
INSERT INTO @DataIn
VALUES
(1,1,'01/01/1980'),
(1,2,'03/01/1981'),
(1,3,'01/02/1991')

DECLARE @WDATA TABLE
(
    QID INT NOT NULL,
    RID INT NOT NULL,
    PID INT NOT NULL        
)
INSERT INTO @WDATA
VALUES
(1,1,101),
(1,1,102),
(1,3,204)

;WITH OuterCTE(QID, RID, PID) AS
(
SELECT 
    ISNULL(D.QID, W.QID) AS QID,
    ISNULL(D.RID, W.RID) AS RID,    
    W.PID
FROM @DataIn AS D FULL OUTER JOIN @WDATA AS W ON W.RID = D.RID AND W.QID = D.QID
)

SELECT
    CTE.QID,
    CTE.RID,
    CTE.PID,
    CASE 
        WHEN COUNT(W.PID) = 0 THEN 'no match'
        WHEN COUNT(W.PID) = 1 THEN 'single match'
        ELSE 'multiple match'
    END
FROM
    OuterCTE AS CTE
    LEFT JOIN @WDATA AS W 
    ON CTE.QID = W.QID 
    AND CTE.RID = W.RID
GROUP BY
    CTE.QID,
    CTE.RID,
    CTE.PID