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(_at_COL int)
RETURNS VARCHAR(30)
AS
BEGIN
 DECLARE _at_W VARCHAR(30)
 DECLARE _at_CASEID INT
 SET _at_W = ''
 SELECT _at_W = @W + CAST(CaseID AS VARCHAR(10)) + ',' FROM tblCases WHERE ClientID=_at_COL
 SELECT _at_CASEID=CASEID FROM tblCases WHERE ClientID=_at_COL  If _at_CASEID IS NOT NULL
 BEGIN
   SET _at_W = LEFT(@W,LEN(@W)-1)
 END
 RETURN _at_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 - 13:43:47 CEST