Join one to many and retrieve single result

Question

I have two tables, in PostgreSQL if that matters, with one to many relations. I need to join them so that for each "one" I only get single result from the "many" table. Not only that but I need to single out specific results from the "many" table.

TABLE_A
ID  | NAME      | DATE          | MORE COLS....
1   | JOHN      | 2012-01-10    | ....
2   | LIZA      | 2012-01-10    | ....
3   | ANNY      | 2012-01-10    | ....
4   | JAMES     | 2012-01-10    | ....
...

TABLE_B
ID  | CODE1     | CODE2     | SORT
1   | 04020     | 85003     | 1
1   | 04030     | 85002     | 4
2   | 81000     | 80703     | 1
3   | 87010     | 80102     | 4
3   | 87010     | 84701     | 5
4   | 04810     | 85003     | 1
4   | 04030     | 85002     | 4
4   | 04020     | 85003     | 1
...

QUERY RESULT
ID  | NAME      | DATE          | CODE1     | CODE2
1   | JOHN      | 2012-01-10    | 04020     | 85003
2   | LIZA      | 2012-01-10    | 81000     | 80703
3   | ANNY      | 2012-01-10    | 87010     | 80102
4   | JAMES     | 2012-01-10    | 04810     | 85003
...

The SORT column in TABLE_B is actually the last char in CODE2 reordered. CODE2 can end with 1-9 but 3 is most important then 5, 7, 4, 2, 1, 0, 6, 8, 9 hence 3-->1, 5-->2, 7-->3 and so forth.

The problem I'm facing is that I need the row from TABLE_B where sort is the lowest number. In some cases there are multiple lowest case (see ID=4 in TABLE_B) then it doesn't matter which of the rows with lowest ID are selected, only that there is single result for that ID.

Welcome to StackOverflow! Thanks for showing data and writing a clear question. Next time, though, you will make life easier on those who want to help you if you show your data in terms of a `CREATE TABLE` statements and `INSERT` or `COPY` statements to load the data. That way people can easily test candidate answers before they post, to make sure they don't have syntax errors and are getting the results you want. — kgrittn, Sep 18 '12 at 03:48

Erwin Brandstetter · Accepted Answer · 2022-12-01T09:53:03.430

18

Simpler, shorter, faster with PostgreSQL's DISTINCT ON:

SELECT DISTINCT ON (a.id)
       a.id, a.name, a.date, b.code1, b.code2
FROM   table_a a
LEFT   JOIN table_b b USING (id)
ORDER  BY a.id, b.sort;

Details, explanation, benchmark and links:

Select first row in each GROUP BY group?

I use a LEFT JOIN, so that rows from table_a without any matching row in table_b are not dropped. Resulting NULL values in b.sort sort last by default.

Aside:
While being allowed in Postgres, it's unwise to use date as column name. It's a reserved word in every SQL standard and a type name in Postgres.

edited Dec 01 '22 at 09:53

answered Sep 18 '12 at 01:43

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you, this solution works too. The naming of the columns was solely for descriptive reasons explicitly for this example. – thorgilsv Sep 18 '12 at 12:19
1

@thorgilsv: I understand. This version should be quite a bit faster. You can test with [`EXPLAIN ANALYZE`](http://www.postgresql.org/docs/current/interactive/sql-explain.html). – Erwin Brandstetter Sep 18 '12 at 12:25

John Woo · Answer 2 · 2012-09-18T00:22:46.457

7

PostgreSQL supports window function. Try this,

SELECT d.ID, d.NAME, d.DATE, d.CODE1, d.CODE2
FROM
(
  SELECT  a.ID, a.NAME, a.DATE,
          b.CODE1, b.CODE2,
          ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY b.SORT ASC, b.CODE2 DESC) AS ROWNUM
  FROM    TableA a
          INNER JOIN TableB b
            ON a.ID = b.ID
) d
WHERE d.RowNum = 1

SQLFiddle Demo

edited Sep 18 '12 at 00:22

answered Sep 17 '12 at 23:55

John Woo

258,903
69
498
492

Thank you, but this gives me the same result as if I'd joined all from TABLE_B to TABLE_A that is many to one. – thorgilsv Sep 18 '12 at 00:16
@user1678791 since you have added `postgresql` tag, i updated the answer using `window function` – John Woo Sep 18 '12 at 00:18
Eureka! Thank you very much, this is the solution to my question. +1 for the SQLFiddle Demo, just fantastic. – thorgilsv Sep 18 '12 at 00:47
1

The fiddle no longer gives the correct result it seems – Joris Mans Dec 30 '17 at 17:08

score 2 · Answer 3 · answered Sep 18 '12 at 00:10

2

Here's what I'd do on SQL Server.

SELECT a.ID,
    a.NAME,
    a.DATE,
    b.CODE1,
    b.CODE2
FROM TABLE_A a
JOIN TABLE_B b
    on a.ID = b.ID
WHERE b.SORT = (SELECT MIN(SORT) 
    FROM TABLE_B
    WHERE ID = b.ID)

answered Sep 18 '12 at 00:10

Bacon Bits

30,782
5
59
66

Join one to many and retrieve single result

3 Answers3

SQLFiddle Demo

Linked