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

Hi All,

Following a bit of off-NG-emailing, Jens has produced a solution (below) - many thanks!

JON

IF OBJECT_id ('fn_Myfunc') IS NOT NULL DROP FUNCTION fn_Myfunc GO

CREATE FUNCTION fn_Myfunc(@COL int)
RETURNS VARCHAR(30)
AS
BEGIN
 DECLARE @W VARCHAR(30)
 DECLARE @CASEID INT
 SET @W = ''
 SELECT @W = @W + CAST(CaseID AS VARCHAR(10)) + ',' FROM tblCases WHERE ClientID=@COL
 SELECT @CASEID=CASEID FROM tblCases WHERE ClientID=@COL  If @CASEID IS NOT NULL
 BEGIN
   SET @W = LEFT(@W,LEN(@W)-1)
 END
 RETURN @W
END
GO

SELECT CL.ClientID, CT.ClientType, dbo.fn_Myfunc(ClientID) as Cases FROM (SELECT DISTINCT ClientID,ClientTypeID FROM tblClients) AS CL INNER JOIN tblClientTypes CT ON CT.CLientTypeID=CL.CLientTypeID Received on Mon Sep 01 2003 - 06:43:47 CDT