Query to combine two 'many:one' tables
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:
I'd also be interested to know how I would produce this result set:
The tables themselves are related via the Cases.ClientID field (as below).
Any help appreciated,
TIA,
JON
CREATE TABLE [Cases] (
++++++++++++++++++++
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
++++++++++++++++++++
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
[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