Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    4

    Unanswered: Join multiple column from different table with one index table

    Halo db folks
    I have a problem like described below :


    Firsttable
    Code:
    Timestamp	column1
    1403481600	403
    1403481900	406
    1403482200	410
    1403482500	409
    1403482800	405
    1403483100	407
    1403483400	407
    1403483700	409
    1403484000	407
    1403484300	405
    1403484600	405
    1403484900	406
    1403485200	385
    Secondtable
    Code:
    Timestamp	column1
    1403481600	346
    1403481900	347
    1403482200	348
    1403482500	347
    1403482800	345
    1403483100	348
    1403483400	346
    1403483700	346
    1403484000	345
    1403484300	346
    1403484600	347
    1403484900	342
    1403485200	319
    Thirdtable
    Code:
    timestamp	column1
    1403481600	188
    1403481900	187
    1403482200	188
    1403482500	188
    1403482800	186
    1403483100	189
    1403483400	186
    1403483700	185
    1403484000	187
    1403484300	186
    1403484600	186
    1403484900	185
    1403485200	182
    Timestamptable

    Code:
    timestamp
    1403481600
    1403481900
    1403482200
    1403482500
    1403482800
    1403483100
    1403483400
    1403483700
    1403484000
    1403484300
    1403484600
    1403484900
    1403485200
    I want to join every second column on Firstable - Thirdtable to one table with timestamp column on Timestamp table as a index table.

    Query :

    Code:
    select TIMESTAMP.timestamp, Firsttable,column1, Secondtable.column1, Thirdtable.column1 from TIMESTAMP, Firsttable, Secondtable, Thirdtable where TIMESTAMP.timestamp
    Expected result should be like :
    Code:
     
    timestamp	col1	col2	col3
    1403481600	403	346	188
    1403481900	406	347	187
    1403482200	410	348	188
    1403482500	409	347	188
    1403482800	405	345	186
    1403483100	407	348	189
    1403483400	407	346	186
    1403483700	409	346	185
    1403484000	407	345	187
    1403484300	405	346	186
    1403484600	405	347	186
    1403484900	406	342	185
    1403485200	385	319	182
    col1 = column1 from Firstable
    col2 = column1 from Secondtable
    col3 = column1 from Thirdtable

    Instead I got this :
    Code:
    1403481600	403	346	188
    1403481600	403	346	187
    1403481600	403	346	188
    1403481600	403	346	188
    1403481600	403	346	186
    1403481600	403	346	189
    1403481600	403	346	186
    1403481600	403	346	185
    1403481600	403	346	187
    1403481600	403	346	186
    1403481600	403	346	186
    1403481600	403	346	185
    1403481600	403	346	182
    1403481600	403	346	187

    Please help, I'm new to db world.

    Thank you.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You don't have a join in your query.

    Code:
    select ts.timestamp, 
           t1.column1, 
           t2.column1, 
           t3.column1 
    from TIMESTAMP ts
      join Firsttable t1 on t1.timestamp = ts.timestamp
      join Secondtable t2 on t2.timestamp = ts.timestamp 
      join Thirdtable t3 on t3.timestamp = ts.timestamp
    For a quick introduction on joining tables please see the tutorial in the Postgres manual: http://www.postgresql.org/docs/curre...rial-join.html

    Btw: "timestamp" is a horrible name for a column (or a table). For one because it's a reserved word but more importantly because it doesn't document what the column contains. A start time? An end time? A due date? A registration time? ....
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jun 2014
    Posts
    4
    Thank you shammat,

    It works.
    and also your suggestion about timestamp column name. I will change it as soon as I found the right name for it.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •