How to join 2 tables to get more data of left table

Question

I am using Postgresql to query data.

What I have done as below:

Table1:

#id #zone_id    #name   #timestamp_create
1   1           Alex    2016-08-10 11:00:11.07+00
2   1           James   2016-08-10 11:30:11.07+00
3   1           Mary    2016-08-10 11:45:11.07+00
4   1           Ken     2016-08-10 12:15:11.07+00
5   1           Aston   2016-08-10 12:45:11.07+00
6   2           Tom     2016-08-11 11:28:11.07+00
7   2           Edward  2016-08-11 11:30:11.07+00

Table2:

#id #zone_id    #code   #timestamp_start            #timestamp_end
1   1           A       2016-08-10 10:50:11.07+00   2016-08-10 11:15:11.07+00
2   1           B       2016-08-10 11:16:11.07+00   2016-08-10 11:50:11.07+00
3   2           E       2016-08-11 10:30:11.07+00   2016-08-11 10:30:11.07+00

My Query:

SELECT t1.zone_id, t1.name, t2.code, t1.timestamp_create
FROM table1 as t1 INNER JOIN table2 as t2 ON t1.zone_id = t2.zone_id
WHERE t1.timestamp_create BETWEEN t2.timestamp_start AND t2.timestamp_end

Result:

#zone_id    #name   #code   #timestamp_create
1           Alex    A       2016-08-10 11:00:11.07+00
1           James   B       2016-08-10 11:30:11.07+00
1           Mary    B       2016-08-10 11:45:11.07+00

How can I achieve the result below:

 #zone_id   #name   #code   #timestamp_create
 1          Alex    A       2016-08-10 11:00:11.07+00
 1          James   B       2016-08-10 11:30:11.07+00
 1          Mary    B       2016-08-10 11:45:11.07+00
 1          Ken             2016-08-10 12:15:11.07+00
 1          Aston           2016-08-10 12:45:11.07+00

I also tried to use LEFT OUTER JOIN, but it still doesn't work.

Any suggestion is appreciated.

score 1 · Accepted Answer · answered May 17 '17 at 10:35

1

Simply switching to left join won't work because you have a condition in your where that involves the columns of the right table. For the rows that don't match, those columns will contain null, so they won't satisfy the condition.

To achieve what you want you have switch to left join and move that condition to the on clause

SELECT  t1.zone_id,
        t1.name,
        t2.code,
        t1.timestamp_create
FROM    table1 as t1
LEFT JOIN
        table2 as t2 
ON      t1.zone_id = t2.zone_id AND
        t1.timestamp_create BETWEEN t2.timestamp_start AND t2.timestamp_end

answered May 17 '17 at 10:35

Stefano Zanini

5,876
2
13
33

Hi Stefano, in case I wanna avoid of null value, how can I replace null with a text, supposed "n/a" or "undefined" – franco phong May 17 '17 at 10:50
1

Ok, I found solution for it. http://stackoverflow.com/questions/27479180/using-coalesce-to-handle-null-values-in-postgresql – franco phong May 17 '17 at 10:57

Gordon Linoff · Answer 2 · 2017-05-17T11:59:04.080

0

You want a left join, but you have to move conditions to the on clause:

SELECT t1.zone_id, t1.name, t2.code, t1.timestamp_create
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t1.zone_id = t2.zone_id AND
        t1.timestamp_create BETWEEN t2.timestamp_start AND t2.timestamp_end

edited May 17 '17 at 11:59

answered May 17 '17 at 10:33

Gordon Linoff

1,242,037
58
646
786

You missed the `LEFT` in your query – Stefano Zanini May 17 '17 at 10:36

How to join 2 tables to get more data of left table

2 Answers2