# How to join two tables using date, time, lat and lon as common field?

I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. Anyhow, I have two tables:

Table 1
id|day|time|lat|lon|
1a|2010-09-12|4:35:04|44.66038|10.74039|
2a|2010-09-12|13:04:38|44.65472|10.76201|
3a|2010-09-12,15:41:55,44.65450|10.76198|
4a|2010-09-12|15:43:54|44.65469|10.76197|
5a|2010-09-12|15:44:26|44.65476|10.76199|
6a|2010-09-12|15:45:40|44.65480|10.76202|
7a|2010-09-18|07:22:00|44.65480|10.76202|
8a|2010-09-18|07:23:57|44.65477|10.76186|
9a|2010-09-18|07:47:32|44.65470|10.76199|

Table 2
id|day|time|lat|lon|depart/arrive
1b|2010-09-12|4:35:04|44.66038|10.74039|D
2b|2010-09-12|15:45:40|44.65480|10.76202|A
3b|2010-09-18|07:47:32|44.65470|10.76199|D

Table 2 has been obtained from table 1 and it includes an additional column: depart/arrive. This column indicates if the coordinate (or row) is the first coordinate of a path (D) or the last coordinate (A).

Using postgres, I would like to use as common field of the two tables "day", "time", "lat", "lon" in order to obtain a new table 3 that includes all the fields of table 1 plus the "depart/arrive" field of table 2.

Table 3
id|day|time|lat|lon|depart/arrive
1a|2010-09-12|4:35:04|44.66038|10.74039|D
2a|2010-09-12|13:04:38|44.65472|10.76201|NULL
3a|2010-09-12,15:41:55,44.65450|10.76198|NULL
4a|2010-09-12|15:43:54|44.65469|10.76197|NULL
5a|2010-09-12|15:44:26|44.65476|10.76199|NULL
6a|2010-09-12|15:45:40|44.65480|10.76202|A
7a|2010-09-18|07:22:00|44.65480|10.76202|NULL
8a|2010-09-18|07:23:57|44.65477|10.76186|NULL
9a|2010-09-18|07:47:32|44.65470|10.76199|D

-
–  lynxlynxlynx Jan 29 '13 at 18:20

Everyone learning SQL should know about the LEFT JOIN. It basically joins everything from the left table to the "joins" on the right side. Where there are no matches, the right data is null, but the left side still is queried. Wikipedia has a good article on Joins.

SELECT A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive
FROM table_1 AS A
LEFT JOIN table_2 AS B ON A.id = B.id AND A.day = B.day AND A.time = B.time AND
A.lat = B.lat AND A.lon = B.lon
ORDER BY A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive;


This isn't "table_3", but you can SELECT ... INTO table_3 FROM ..., or create a view, so the joined data is always up-to-date in something that looks like a table.

On a side note, rather than storing two columns for day and time, it is more powerful to use one column for timestamp (with time zone). Same for your location data, which could be stored as a PostgreSQL point type, or PostGIS geometry or geography types. These will add other GIS features, and can improver the searching performance with an optimized index.

-
Using your SELECT after CREATE TABLE table_3 AS ... should do the trick. –  underdark Jan 29 '13 at 21:08

you have the right idea but would need another field to concat them all into.
or since you are in posgres perhaps a view with the fields concat.
once you have the field the join will be completed as lynxlynxlynx shows.

EDIT:

1. create a composite pk and fk. It may work from what I read.
2. use the join
-
why concat? simple joins should do. –  underdark Jan 29 '13 at 19:54
@underdark, Are you suggesting to join against both ID fields or some other means of joining day, time, lat, lng without concat? Can we assume ID between two tables are the same? –  artwork21 Jan 29 '13 at 20:07
IF the OP is a savvy as yourself they may find the ability to create composite primary keys. however the creation of composite foreign key would be the next thing on the agenda. I only learned about them minutes ago. and I have been around databases a while. My software stack and expertise would still lead me to create my own composite field using concat. - downvote really? :( -w3resource.com/sql/joins/… –  Brad Nesom Jan 29 '13 at 20:20
I agree with @artwork the question as stated mentions joining more than one column. Does postgres support composite pK and fk? –  Brad Nesom Jan 29 '13 at 20:24
@artwork21 Mike already posted an answer relying only on SQL without any functions like concat. Imho, concat might be more obvious for users with a background in using spreadsheets but I don't think it should be recommended in a database setting. –  underdark Jan 29 '13 at 21:12

Thank you all. The query works and I could visualize the result in the data output windows. In order to obtain table_3 I used SELECT A.id, A.day, A.time, A.lat, A.lon, B.depart_arrive INTO table_3 ...

-