I have below query, with 3 tables
1. Client table - (fields = ID (primary) , Surname)
2. Case table - (fields = ID (primary), Reference)
3. ClientCase-Join - joining table (fields = ID (primary), ClientID (foreign), CaseID (foreign))
ClientCase-Join lists all the clients in each case, using foreign keys to the primary keys of tables Client, Case.
If I have 3 Clients AA,BB,CC in Case 11 I get 3 rows showing each Client name next to Case.
ie.
** Where I have multiple Clients in a Case, I would just like it to show a SINGLE row of the first Client name with '+' sign next to it, indicating multi client case.
ie.
** If possible, it would also be good to have a number returned and stored in a field in Case table. eg. Case.NumberClients = 3
Thanks in advance as Sql confuses the heck out of this newbie.
1. Client table - (fields = ID (primary) , Surname)
2. Case table - (fields = ID (primary), Reference)
3. ClientCase-Join - joining table (fields = ID (primary), ClientID (foreign), CaseID (foreign))
Code:
SELECT Client.Surname, Case.Reference
FROM [Case]
INNER JOIN (Client
INNER JOIN [ClientCase-Join]
ON Client.[ID] = [ClientCase-Join].[ClientID])
ON Case.[ID] = [ClientCase-Join].[CaseID];
ClientCase-Join lists all the clients in each case, using foreign keys to the primary keys of tables Client, Case.
If I have 3 Clients AA,BB,CC in Case 11 I get 3 rows showing each Client name next to Case.
ie.
Client - Case
AA, 11
BB, 11
CC, 11
** Where I have multiple Clients in a Case, I would just like it to show a SINGLE row of the first Client name with '+' sign next to it, indicating multi client case.
ie.
Clients - Case
AA+, 11
** If possible, it would also be good to have a number returned and stored in a field in Case table. eg. Case.NumberClients = 3
Thanks in advance as Sql confuses the heck out of this newbie.
Last edited: