-1

Say I have two tables named Customers and Banks.

  • Customers has Id(PK), Name and BankCode
  • Banks has Id(PK), Name and Code

BankCode column in Customers table is a loose reference to Code column in Banks table. Code is not a foreign key so there are multiple records with the same Code. Sample data is like this:

Persons:

Id Name BankCode
1 Jack 2
2 Jane 2
3 John 5

Banks:

Id Name Code
1 National 2
2 National Subsidiary 2
3 GNB 3
4 Global Banking 5

I need to get a list of persons with their bank name attached, I tried simply joining tables like this:

SELECT P.Id, P.Name, P.BankCode, B.Name
FROM Persons P
   JOIN Banks B
      ON P.BankCode = B.Code

But this query results in redundant records for persons whose bank code isn't unique. In this case Jack and Jane will each have two similar records with different bank names.

Since the banks with the same code are a family and have similar names, how can I manipulate the query to result only one record for each person (only use one bank name and ignore others)?

Hamid Siaban
  • 346
  • 4
  • 13
  • 1
    It seems you need a BankFamily table with a foreign key reference. Working around the data model gap in a query is a kludge at best. – Dan Guzman Aug 30 '22 at 13:54
  • 1
    What is expected result from this sample? – Nenad Zivkovic Aug 30 '22 at 14:32
  • Seems to me that in table Persons you should have a column BankID, not BankCode – GuidoG Aug 30 '22 at 14:45
  • Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) – philipxy Aug 30 '22 at 19:35

1 Answers1

1

If you don't want to fix the design of your tables,
than the only way I can think of is to show all banks with that code for each user,
like this

select p.id,
       p.Name,
       ( select string_agg(b.Name, ', ')
         from   Banks b
         where  b.BankCode = p.BankCode
       ) as Banks
from   Persons p

This will look like this

id Name Banks
1 Jack National, National Subsidiary
2 Jane National, National Subsidiary
3 John Global Banking

See a working DBFiddle here

GuidoG
  • 11,359
  • 6
  • 44
  • 79