Query to combine two 'many:one' tables

From: Jon Maz <jonmaz_at_NOSPAM.surfeu.de>
Date: Fri, 29 Aug 2003 15:40:47 +0200
Message-ID: <binks7$mdj$1_at_online.de>



Hi,

I'm hoping someone can help me with an SQL query (SQL Server 2000).

There is a many:one relationship between my Cases table and my Clients table (a Client can have n Cases). I'd like to combine the Cases and Clients table to give the following result set:

++++++++++++++++++++

TARGET RESULT SET 1
++++++++++++++++++++

COL1     COL2           COL3       COL4        COL5
ClientID ClientName CaseID#1 CaseID#2 CaseID#3
1             Smith             2                4                16
2             Jones             3                7                <NULL>
3             Williams        14              18               24



I'd also be interested to know how I would produce this result set:

++++++++++++++++++++

TARGET RESULT SET 2
++++++++++++++++++++

COL1        COL2               COL3
ClientID     ClientName       CaseID's
--------------------------------------
1                Smith                 2,4,16
2                Jones                 3,7
3                Williams             14,18,24


The tables themselves are related via the Cases.ClientID field (as below).

Any help appreciated,

TIA, JON


CREATE TABLE [Cases] (
 [CaseID] [int] IDENTITY (1, 1) NOT NULL ,  [ClientID] [int] NOT NULL ,
 CONSTRAINT [PK_Cases] PRIMARY KEY CLUSTERED  (
[CaseID]

 ) ,
 CONSTRAINT [FK_Cases_Clients] FOREIGN KEY  (
[ClientID]

 ) REFERENCES [Clients] (
[ClientID]

 )

CREATE TABLE [Clients] (
 [ClientID] [int] IDENTITY (1, 1) NOT NULL ,  CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED  (
[ClientID]

 ) ,
) Received on Fri Aug 29 2003 - 15:40:47 CEST

Original text of this message