Reducing rows in sql query (1 Viewer)

lawman

New member
Local time
Today, 12:58
Joined
Dec 8, 2020
Messages
15
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))

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:

arnelgp

once i caught a fish alive...
Local time
Today, 19:58
Joined
May 7, 2009
Messages
13,202
What if you re arrange the declaration of your tables:
Code:
SELECT Client.Surname, Case.Reference FROM [ClientCase-Join] INNER JOIN (Client INNER JOIN [Case] ON Client.[ID] = [ClientCase-Join].[ClientID]) ON Case.[ID] = [ClientCase-Join].[CaseID];
 

plog

Banishment Pending
Local time
Today, 06:58
Joined
May 11, 2011
Messages
10,479
If possible, it would also be good to have a number returned and stored in a field in Case table. eg. Case.NumberClients = 3

First, that's not how databases work. You don't save values that are calculable. Instead, you calculate them in a query and refer to your query for those values.

Second, only use alphanumeric characters in table/field names. "ClientCase-Join" is a poor name because of that dash. It just makes coding and querying more difficutl. I suggest "ClientsCases"

Finally, to achieve what you want, you will need a subquery:

Code:
SELECT [ClientCase-Join].CaseID, Min([ClientCase-Join].ClientID) AS MinClient, Count([ClientCase-Join].ClientID) AS Clients
FROM [ClientCase-Join]
GROUP BY [ClientCase-Join].CaseID;

Paste that into a new query and name it 'sub1'. It determines what surname to show for each Reference and lets you know which ones have multiples. Then the below query will give you the results you want:

Code:
SELECT [Surname] & IIf([Clients]>1,"+") AS SurnamePlus, Case.Reference
FROM ([Case] INNER JOIN sub1 ON Case.CaseID = sub1.CaseID) INNER JOIN Client ON sub1.MinClient = Client.ClientID;
 

Users who are viewing this thread

Top Bottom