Read from 3 tables

Discussion in 'MySQL' started by mosbif, Jun 20, 2016.

0
  1. #1
    I pick some data from three tables TEST_DATA, TEST_PARAM and TEST_TEST. When I run the question I get more rows with the same information, (serial number and attribute), except from the date/time column.

    How does a SQL question look like to get only the latest value for each pt_param_attr based on the date column? I dont want to have the same serialno. with the same attr. For examle 8775-001 has two attr '4' and four attr '22'. I only want to have the latest for each attr and each serial.
    Please Help!

    I use Sybase, SQL Anywhere 12

    My query:
    SELECT pt_test_man_order, pt_test_serial, pt_data_var, pt_param_attr, pt_data_date
    FROM test_data,
    test_param,
    test_test
    WHERE pt_test_id = pt_data_test_id
    AND pt_data_param_id = pt_param_id
    AND ((pt_test_man_order='8775'))
    ORDER BY pt_test_serial

    I get this answer: SQL answer.png
     
    mosbif, Jun 20, 2016 IP
  2. Zoidrums

    Zoidrums Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #2
    First of all, can you put any aliases on your tables in your query? Like this :

    SELECT TST.pt_test_man_order,
    TST.pt_test_serial,
    DATA.pt_data_var,
    PRM.pt_param_attr,
    DATA.pt_data_date
    FROM test_data DATA,
    test_param PRM,
    test_test TST
    WHERE TST.pt_test_id = DATA.pt_data_test_id
    AND DATA.pt_data_param_id = PRM.pt_param_id
    AND TST.pt_test_man_order = '8775'
    ORDER BY TST.pt_test_serial;

    We don't know your database, it will help us to understand the problem.

    Then, can you explain your need? Can your show us the content of these tables?
     
    Zoidrums, Feb 9, 2017 IP