Re: Query to combine two 'many:one' tables

From: Jon Maz <jonmaz_at_NOSPAM.surfeu.de>
Date: Fri, 29 Aug 2003 18:16:29 +0200
Message-ID: <binu05$vda$1_at_online.de>

Hi All,

Thanks for the help so far! By plundering code from the thread Jens referred me to, I'm managing to make some (slow) progress, and have produced some SQL which works ("NEW QUERY ON CASES TABLE", below).

I now need to bolt this query on to a pre-existing query of the Clients table (also below), and am struggling to do this. Can anyone help me join these two queries?

Much appreciated,

JON

---

PRE-EXISTING QUERY ON CLIENTS TABLE

---

SELECT Clients.*, ClientTypes.ClientType FROM tblClients Clients
LEFT OUTER JOIN tblClientTypes ClientTypes ON

    Clients.ClientTypeID = ClientTypes.ClientTypeID

---

NEW QUERY ON CASES TABLE (needs to be joined somehow to the above query)

---

SELECT ClientID, CAST(MIN(CASE seq WHEN 1 THEN CaseID END) As VarChar) +

 COALESCE(',' + CAST(MIN(CASE seq WHEN 2 THEN CaseID END) As VarChar), '') +
 COALESCE(',' + CAST(MIN(CASE seq WHEN 3 THEN CaseID END) As VarChar), '') +
 COALESCE(',' + CAST(MIN(CASE seq WHEN 4 THEN CaseID END) As VarChar), '') +
 COALESCE(',' + CAST(MIN(CASE seq WHEN 5 THEN CaseID END) As VarChar), '')
             AS CaseIDs

FROM
(
 SELECT Cases1.ClientID, Cases1.CaseID, COUNT(*) AS seq  FROM tblCases AS Cases1
 LEFT JOIN tblCases AS Cases2 ON

    Cases1.ClientID = Cases2.ClientID AND Cases1.CaseID >= Cases2.CaseID  GROUP BY Cases1.ClientID, Cases1.CaseID )
AS S
GROUP BY ClientID

---

TARGET RESULT SET

---

ClientID ClientType CaseIDs

---

1                 P                     1,3,5
2                 P                      2,4
3                 C                      <NULL>

Received on Fri Aug 29 2003 - 18:16:29 CEST