postgresql join 2 tables

Question

i have 2 tables tab1 and tab2, tab2(tab1_id) references tab1(id)

tab2 has different values for the tab1(id)

i need a join which will join tab1 with action column from tab2, and latest value for the id.

tab1 :-


 id | user_file_id |    created_date     |    modified_date    
----+--------------+---------------------+---------------------
  2 |          102 | 2012-01-12 01:23:46 | 2012-03-04 16:52:28
  4 |          104 | 2012-01-12 15:45:10 | 2012-01-15 02:23:40
  6 |          106 | 2012-01-18 00:14:34 | 2012-01-24 20:17:49
  7 |          107 | 2012-02-02 01:07:14 | 2012-04-17 09:29:17
  8 |          108 | 2012-02-15 13:16:24 | 2012-03-26 10:30:51
  9 |          109 | 2012-02-20 18:08:48 | 2012-04-09 06:14:58
 10 |          110 | 2012-02-24 20:49:10 | 2012-03-23 11:36:41
 11 |          111 | 2012-03-05 22:38:14 | 2012-03-16 04:29:35
(8 rows)


tab2:-

 id | action | tab1_id 
----+--------+---------
  1 |      1 |       2
  3 |      2 |       2
  4 |      1 |       2
  5 |      2 |       2
  6 |      1 |       2
  7 |      3 |       2
  2 |      1 |       4
  8 |      1 |       6
  9 |      1 |       7
 10 |      1 |       8
 11 |      1 |       9
 12 |      1 |      10
 13 |      1 |      11
(13 rows)

the both tab1 and tab2 joined to get the output as :-

id | user_file_id |    created_date     |    modified_date    | action 
----+--------------+---------------------+---------------------+--------
  2 |          102 | 2012-01-12 01:23:46 | 2012-03-04 16:52:28 |      3
  4 |          104 | 2012-01-12 15:45:10 | 2012-01-15 02:23:40 |      1
  6 |          106 | 2012-01-18 00:14:34 | 2012-01-24 20:17:49 |      1
  7 |          107 | 2012-02-02 01:07:14 | 2012-04-17 09:29:17 |      1
  8 |          108 | 2012-02-15 13:16:24 | 2012-03-26 10:30:51 |      1
  9 |          109 | 2012-02-20 18:08:48 | 2012-04-09 06:14:58 |      1
 10 |          110 | 2012-02-24 20:49:10 | 2012-03-23 11:36:41 |      1
 11 |          111 | 2012-03-05 22:38:14 | 2012-03-16 04:29:35 |      1
(8 rows)

user359040 · Accepted Answer · 2012-02-27 11:47:19Z

2

Try:

select t1.*, t2.action
from tab1 t1
join (select t.*,
             row_number() over (partition by tab1_id order by id desc) rn
      from tab2 t) t2
  on t1.id = t2.tab1_id and t2.rn = 1

Change the join to a left join if you want to allow for a row on tab1 having no actions recorded on tab2.

answered Feb 27, 2012 at 11:47

user359040

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2012-02-27 13:38:07Z

1

SELECT tab1.*, t2.action
FROM  tab1
JOIN (
    SELECT DISTINCT ON (tab1_id) tab1_id
         , first_value(action) OVER (PARTITION BY tab1_id
                                     ORDER BY id DESC) AS action
    FROM   tab2
    ) t2 ON tab1.id = t2.tab1_id

@Mark already mentioned the alternative LEFT JOIN.

answered Feb 27, 2012 at 13:38

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

1 Comment

MAHI Over a year ago

can you help me with this

Collectives™ on Stack Overflow

postgresql join 2 tables

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related