Agfdhyk

I have a table, let's call it Case the case table contains basic information about a case, such as the CaseNumber and the CaseOwner.

In a second table CasePersons, details about individuals attached to a particular case number are held. This includes PersonId, FirstName and LastName. CasePersons links to Case through CaseNumber.

In a third table, ContactDetails a number of phone numbers associated with a particular person are stored. The Type column within ContactDetails represents the type of phone number e.g. Home, mobile or work. ContactDetails links to CasePersons through PersonId

More clearly:

Case

------------------------

CaseNumber     CaseOwner

1              Owner1

2              Owner2

3              Owner3



CasePersons

---------------------

PersonId        CaseNumber      FirstName     LastName

1               1               Bob           Person

2               1               Jim           Human

3               2               Gary          Man

4               3               Pete          Smith

5               3               Matt          Jones





CaseDetails

-------------------------

PersonId       Detail        Type

1              0123456789    1

1              1111111111    2

2              2222222222    1

1              0101001011    3

2              1234123412    2

3              0000011111    1

3              1231231231    2

I want to be able to write a query that can pull back the basic details of a case, as well as all of the individuals associated with a case and ALL of the associated phone numbers.

How exactly would I construct a query to extract this information? I can't seem to find the information on this on Google as I'm not sure what to search for.

The whole point of this is so that I can find all of the associated numbers for a particular case and store them in one location.

Expected output

CaseNumber    CaseOwner    Person1FirstName     Person1LastName     Person1HomeNumber    Person1MobileNumber     Person1WorkNumber     Person2FirstName     Person2LastName     Person2HomeNumber    Person2MobileNumber     Person2WorkNumber  

1             Owner1       Bob                  Person              0123456789           1111111111              0101001011            Jim                  Human               2222222222           1234123412

If you're okay with all the information for one case showing up in multiple rows, you can use multiple joins and an order by to place related rows together.

select

    Case.CaseNumber

    ,CaseOwner

    ,CasePersons.PersonID

    ,FirstName

    ,LastName

    ,Detail

    ,Type

from

    Case

    join CasePersons

    on Case.CaseNumber=CasePersons.Casenumber

    join CaseDetails

    on CasePersons.PersonID=CaseDetails.PersonID

Order by

    Case.CaseNumber

    ,CasePersons.PersonID

Just joining on the respective columns of the tables would do.

Eg:
Step 1:You get all of the case information from "Case" table so this would be your driving table.
Step 2: To get all the persons involved in your case, join with the "CasePersons" table
case.CaseNumber=casepersons.CaseNumber
Step 3: Now you got all persons associated with a case as the result of step 2. After this you are interested in extracting the phone numbers of the persons who are involved in your case,so join
casepersons.personid=casedetails.personid

Query

 select case.CaseNumber

           ,case.CaseOwner

           ,casepersons.personid

           ,casepersons.firstname

           ,casepersons.lastname

           ,casedetails.Detail

           ,casedetails.Type

           ,case when casedetails.Type=1 then 'Home'

                 when casedetails.Type=2 then 'Mobile'

                 when casedetails.Type=3 then 'Work'

            end as phone_type 

      from case 

      join casepersons

        on case.CaseNumber=casepersons.CaseNumber

      join (select personid

                   ,max(case when casedetails.Type=1 then details end) as Home_number

                   ,max(case when casedetails.Type=2 then details end) as Work_number

                   ,max(case when casedetails.Type=3 then details end) as Mobile_number

              from casedetails

          group by personid) as cd_1

        on casepersons.personid=cd_1.personid

you can check the following script. I have added dynamic table which is related with maximum person added to the CasePersons table for each case.

/*

DROP TABLE #Case;

DROP TABLE #CasePersons;

DROP TABLE #CaseDetails;

DROP TABLE #CaseTemp;

*/

CREATE TABLE #Case

(

    CaseNumber  INT

    ,CaseOwner  VARCHAR(100)

);



INSERT INTO #Case VALUES(1,'Owner1'),(2,'Owner2'),(3,'Owner3');



CREATE TABLE #CasePersons

(

    PersonId    INT

    ,CaseNumber INT

    ,FirstName  VARCHAR(100)

    ,LastName   VARCHAR(100)

);



INSERT INTO #CasePersons

VALUES(1,1,'Bob','Person'),(2,1,'Jim','Human'),(3,2,'Gary','Man'),(4,3,'Pete','Smith'),(5,3,'Matt','Jones');



CREATE TABLE #CaseDetails

(

    PersonId    INT

    ,Detail     VARCHAR(100)

    ,[TYPE]     INT

);



INSERT INTO #CaseDetails

VALUES

(1,'0123456789',1)

,(1,'1111111111',2)

,(2,'2222222222',1)

,(1,'0101001011',3)

,(2,'1234123412',2)

,(3,'0000011111',1)

,(3,'1231231231',2);







    SELECT c.CaseNumber

            ,c.CaseOwner

            ,ROW_NUMBER() OVER (PARTITION BY C.CaseNumber ORDER BY CD.PersonId) RowNumber

            ,CP.PersonId

            ,CP.FirstName

            ,CP.LastName

            ,CD.HomeNumber

            ,CD.MobileNumber

            ,CD.WorkNumber

    INTO #CaseTemp

    FROM #Case C

    LEFT JOIN #CasePersons CP ON C.CaseNumber = CP.CaseNumber

    LEFT JOIN 

    (

        SELECT PersonId

                ,MAX(CASE WHEN TYPE=1 THEN Detail END) HomeNumber 

                ,MAX(CASE WHEN TYPE=2 THEN Detail END) MobileNumber 

                ,MAX(CASE WHEN TYPE=3 THEN Detail END) WorkNumber 

        FROM #CaseDetails

        GROUP BY PersonId

    ) CD ON CP.PersonId = CD.PersonId

    --WHERE CD.TYPE=1

    ORDER BY C.CaseNumber

             ,CP.PersonId



--Create result table            

DROP TABLE #CaseList

GO

CREATE TABLE #CaseList

(

    CaseNumber  INT

    ,CaseOwner  VARCHAR(100)

    ,Person1FirstName   VARCHAR(100)

    ,Person1LastName    VARCHAR(100)

    ,Person1HomeNumber  VARCHAR(100)

    ,Person1MobileNumber VARCHAR(100)

    ,Person1WorkNumber  VARCHAR(100)

)



--Insert first person details to the table

INSERT INTO #CaseList

SELECT   CaseNumber

        ,CaseOwner

        ,FirstName

        ,LastName

        ,HomeNumber

        ,MobileNumber

        ,WorkNumber

FROM #CaseTemp

WHERE RowNumber=1



--Create additional person fields

    DECLARE @SQLString  VARCHAR(MAX)=''

    DECLARE @MaxPerson  INT=(SELECT MAX(RowNumber) FROM #CaseTemp)

    DECLARE @Seq    int=2



    --

    WHILE @MaxPerson>=@Seq

    BEGIN

        SET @SQLString='ALTER TABLE #CaseList ADD Person'+CAST(@Seq AS VARCHAR)+'FirstName VARCHAR(100);'

        SET @SQLString=@SQLString+CHAR(13)+'ALTER TABLE #CaseList ADD Person'+CAST(@Seq AS VARCHAR)+'LastName  VARCHAR(100);'

        SET @SQLString=@SQLString+CHAR(13)+'ALTER TABLE #CaseList ADD Person'+CAST(@Seq AS VARCHAR)+'HomeNumber  VARCHAR(100);'

        SET @SQLString=@SQLString+CHAR(13)+'ALTER TABLE #CaseList ADD Person'+CAST(@Seq AS VARCHAR)+'MobileNumber  VARCHAR(100);'

        SET @SQLString=@SQLString+CHAR(13)+'ALTER TABLE #CaseList ADD Person'+CAST(@Seq AS VARCHAR)+'WorkNumber  VARCHAR(100);'

        SET @Seq +=1

    END



    EXEC (@SQLString)



--Update additional person details 

SET @SQLString=''



SET @Seq=2

WHILE @MaxPerson>=@Seq

BEGIN

    SET @SQLString=@SQLString+CHAR(13)+'UPDATE #CaseList SET Person'+CAST(@Seq AS VARCHAR)+'FirstName   =CT.FirstName 

                                                            ,Person'+CAST(@Seq AS VARCHAR)+'LastName    =CT.LastName

                                                            ,Person'+CAST(@Seq AS VARCHAR)+'HomeNumber  =CT.HomeNumber

                                                            ,Person'+CAST(@Seq AS VARCHAR)+'MobileNumber=CT.MobileNumber

                                                            ,Person'+CAST(@Seq AS VARCHAR)+'WorkNumber  =CT.WorkNumber                                                      

                                        FROM #CaseTemp CT 

                                        WHERE #CaseList.CaseNumber=CT.CaseNumber AND CT.RowNumber='+CAST(@Seq AS VARCHAR)+''

    SET @Seq +=1

END



EXEC (@SQLString)



--SELECT * FROM #CaseTemp

SELECT * FROM #CaseList