SQL Query with Inner Joins and multiple selects

Transact-SQL
1

Hi All,
I am in need of an SQL Query that will output 10 columns from 3 tables.
I have 3 tables

Table 1: Tc
Table 2: Tcp
Table 3: Tca

The join between the 3 tables is the ClientID which is in each table.

These are the columns;

[Tc]![ClientID]
[Tc]![Forename]
[T_Client]![Surname]

[Tcp]![ConsentTypeID] where = 3
[Tcp]![OptState]
[Tcp]![UpdatedDate]

[Tca]![ClientAddInfoID] where =18
[Tca]![ClientAddInfoData]

[Tca]![ClientAddInfoID] where =25
[Tca]![ClientAddInfoData]

The Tca table the data is I think what they call denormalized you might for example have the entries

image

this is why I have mentioned for the output of the query to give specific names based on the criteria being searched.

This is the output I am looking for

[ClientID],[Forename],[Surname],[ConsentTypeID],[OptState],[UpdatedDate],[ClientAddInfoID18],[ClientAddInfoData18],[ClientAddInfoID25],[ClientAddInfoData25]

Please help.

2 
SELECT C.ClientID, C.Forename, C.Surname, P.ConsentTypeID, P.OptState, P.UpdatedDate
,MAX(CASE WHEN A.ClientAddInfoID=18 THEN A.ClientAddInfoID END) AS ClientAddInfoID18
,MAX(CASE WHEN A.ClientAddInfoID=18 THEN A.ClientAddInfoData END) AS ClientAddData18
,MAX(CASE WHEN A.ClientAddInfoID=25 THEN A.ClientAddInfoID END) AS ClientAddInfoID25
,MAX(CASE WHEN A.ClientAddInfoID=25 THEN A.ClientAddInfoData END) AS ClientAddData25
FROM TC C
INNER JOIN TCP P ON C.ClientID=P.ClientID
INNER JOIN TCA A ON C.ClientID=A.ClientID
WHERE P.ConsentTypeID=3
AND A.ClientAddInfoID IN (18,25)
GROUP BY C.ClientID, C.Forename, C.Surname, P.ConsentTypeID, P.OptState, P.UpdatedDate

That should provide the output you requested.

In future, please refer to this and follow its recommendations when posting:

3

Hi Robert,

There was also a table missing that needs to be included
[Tcp]![ConsentTypeID] where = 3
[Tcp]![OptState]
[Tcp]![UpdatedDate]

There should be 8 columns output in the query as follows

[ClientID],
[Forename],
[Surname],
[ConsentTypeID],
[OptState],
[UpdatedDate],
[ClientAddInfoData18], (this is where the ClientAddInfoID = 18)
[ClientAddInfoData25] (this is where the ClientAddInfoID = 25)

Thanks

4

Those are all included in my original reply.

5

Hi Robert,

Thanks it was working.

I would like to add 2 more columns on the output to the query please;

In the table

TE.EmailAddressID
TE.Email

which is joined on TC C ON C.EmailID=TE.EmailAddressID

If you would please be kind enough to amend the query to include the above 2 columns from the TE table.

Many thanks