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

Hi Jens,

Here's some DDL / Data for you to play with!

Cheers,

JON

IF OBJECT_id ('tblCases') IS NOT NULL DROP TABLE tblCases CREATE TABLE tblCases (CaseID INTEGER, ClientID INTEGER, PRIMARY KEY
(CaseID))

INSERT INTO tblCases VALUES (1, 1)
INSERT INTO tblCases VALUES (2, 1)
INSERT INTO tblCases VALUES (3, 1)
INSERT INTO tblCases VALUES (4, 2)
INSERT INTO tblCases VALUES (5, 2)
INSERT INTO tblCases VALUES (6, 3)
INSERT INTO tblCases VALUES (7, 4)
INSERT INTO tblCases VALUES (8, 4)
INSERT INTO tblCases VALUES (9, 4)
INSERT INTO tblCases VALUES (10,4)
INSERT INTO tblCases VALUES (11,4)
INSERT INTO tblCases VALUES (12,6)

IF OBJECT_id ('tblClients') IS NOT NULL DROP TABLE tblClients CREATE TABLE tblClients (ClientID INTEGER, ClientTypeID INTEGER, PRIMARY KEY
(ClientID))

INSERT INTO tblClients VALUES (1, 1)
INSERT INTO tblClients VALUES (2, 1)
INSERT INTO tblClients VALUES (3, 2)
INSERT INTO tblClients VALUES (4, 2)
INSERT INTO tblClients VALUES (5, 2)
INSERT INTO tblClients VALUES (6, 1)

IF OBJECT_id ('tblClientTypes') IS NOT NULL DROP TABLE tblClientTypes CREATE TABLE tblClientTypes (ClientTypeID INTEGER, ClientType VARCHAR(50), PRIMARY KEY (ClientTypeID))

INSERT INTO tblClientTypes VALUES (1, 'Person') INSERT INTO tblClientTypes VALUES (2, 'Company')

ALTER TABLE [dbo].[tblCases] ADD
 CONSTRAINT [FK_tblCases_tblClients] FOREIGN KEY ([ClientID]) REFERENCES [dbo].[tblClients] ([ClientID])
GO

ALTER TABLE [dbo].[tblClients] ADD
 CONSTRAINT [FK_tblClients_tblClientTypes] FOREIGN KEY ([ClientTypeID]) REFERENCES [dbo].[tblClientTypes] ([ClientTypeID]) GO

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

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

ClientID ClientType CaseID's

1            Person       1,2,3
2            Person       4,5
3            Company   6
4            Company   7,8,9,10,11
5            Company   <NULL>
6            Person       12