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.
In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
SQL LEFT JOIN examples Each location belongs to one and only one country while each country can have zero or more locations. The relationship between the countries and locations tables is one-to-many.
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 in this closely related answer.
I use a LEFT JOIN
, so that rows from table_a
without any matching row in table_b
are not dropped.
Side notes:
While being allowed in PostgreSQL, it's unwise to use date
as column name. It's a reserved word in every SQL standard and a type name in PsotgreSQL.
It's also an anti-pattern to name an ID column id
. Not descriptive and not helpful. One (of many) possible naming convention would be to name it after the table where it is primary key: table_a_id
. Same name for foreign keys referencing it (if no other natural name takes precedence).
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With