0

I'm trying to create an sql query that takes records from a File table and a Customer table. A file can have multiple customers. I want to show only one record per File.id and Concatenate the last names based on alphabetical order of the clients if the names are different or only show one if they are the same.

Below is a picture of the Relationship.

Table Relationship

The results from my query look like this currently.

enter image description here

I would like the query to look like this.

File ID Name
1 Dick Dipe
2 Bill
3 Lola

Originally I had tried doing a subquery but I had issues that there were multiple results and it couldn't list more than one. If I could do a loop and add to an array, I feel like that would work.

If I were to do it in Python, I would write this but when I try to translate that into SQL, I get errors that either the subquery can only display one result or the second name under file two gets cut off.

clients = ['Dick','Dipe','Bill','Lola', 'Lola']
files = [1,2,3]
fileDetails = [[1,0],[1,1],[2,2],[3,3],[3,4]]

file_clients = {}
for file_id, client_index in fileDetails:
    if file_id not in file_clients:
        file_clients[file_id] = []
    client_name = clients[client_index]
    file_clients[file_id].append(client_name)

for file_id, client_names in file_clients.items():
    client_names = list(dict.fromkeys(client_names))
    client_names_string = " ".join(client_names)
    print(f"File {file_id}: {client_names_string}")
June7
  • 19,874
  • 8
  • 24
  • 34
GUMBOTHEFROG
  • 1
  • SQL alone cannot do this in Access. So either use your Python procedure or VBA. Review https://stackoverflow.com/questions/18940038/concatrelated-function-in-a-query. If your data was in SQLServer or MySQL, query could do it alone. – June7 Jan 24 '23 at 06:51

0 Answers0