How to do an outer join with count based column?

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 t开发者_Go百科o 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”

---

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??

---

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

---

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