CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-services
marhom
marhom

Reputation: 25

SQL statement - difficulty producing two fields from one table

I am relatively new to SQL and able to write some simple statements with some JOIN, WHERE thrown in. I also have some experience with SSRS 2008. However I am having difficulty producing two (unique) lists of names based on one table.

I am trying to produce a report containing Staff Members which lists Clients they look after. All names (regardless whether Staff or Client) are held in the Person table. I can run a simple query listing all staff members or clients but I am unable to list both.

To produce the Client list my query would be simply

SELECT p.Forenames, p.Surname
FROM Person AS p
INNER JOIN Client AS c ON p.ID = c.ID

and to produce the Staff list my query would be as above but the INNER JOIN as follows:

INNER JOIN StaffMember AS s ON p.ID = s.ID

The link between Staff Member and Client with all the different links are as follows (for reference).

Client.ID = ClientRecordForm.Client_ID
Person.ID = ClientRecordForm.AssignedSupportWorker_ID
Person.ID = StaffMember.ID
StaffMember.ID = ClientRecordForm.AssignedSupportWorker_ID

To help illustrate this, I can run a query to list Staff Members who have been assigned to Clients.

SELECT DISTINCT p.Forenames, p.Surname
FROM Person AS p
INNER JOIN ClientRecordForm AS crf ON p.ID = crf.AssignedSupportWorker_ID

This last query is essentially what I want but I am struggling to add the Client names as I don't seem to be able to distinguish the Clients as I am already using the Person table

Upvotes: 1

Views: 54

Answers (2)

crthompson
crthompson

Reputation: 15865

If you want to show persons and be able to distinguish clients as well, try a self join.

SELECT DISTINCT p.Forenames, p.Surname
FROM Person AS p
INNER JOIN ClientRecordForm AS crf ON p.ID = crf.AssignedSupportWorker_ID
inner join person as personClients on crf.clientid = personClients.id

As you can see, you could then join another persons table to get StaffMember, you can join from personClients to Client to get information there ...etc.

Upvotes: 1

dario
dario

Reputation: 5269

Is that what you want?

SELECT DISTINCT p.Forenames, p.Surname
FROM Person AS p
INNER JOIN ClientRecordForm AS crf 
    ON p.ID = crf.AssignedSupportWorker_ID
INNER JOIN Client AS c
    ON c.ID = crf.Client_ID

Upvotes: 0

Related Questions