Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Join before the join

    If I had a 3 tables like this

    Table a (parent device)
    log_time | serial | code_id

    Table b (child device)
    log_time | serial | device_index | parent_serial | code_id

    Table c (code decipher)
    id | name

    Then ran the following query:

    SELECT a.log_time,a.serial,ch1.serial,,ch2.s erial,,ch3.serial,
    FROM a
    LEFT JOIN b AS ch1 ON a.serial=ch1.parent_serial AND a.log_time=ch1.log_time AND device_index='1'
    LEFT JOIN b AS ch2 ON a.serial=ch2.parent_serial AND a.log_time=ch2.log_time AND device_index='2'
    LEFT JOIN b AS ch3 ON a.serial=ch3.parent_serial AND a.log_time=ch3.log_time AND device_index='3'
    LEFT JOIN c AS ch1_code ON
    LEFT JOIN c AS ch2_code ON
    LEFT JOIN c AS ch3_code ON
    WHERE a.serial="blah1" AND a.log_time>"blah2" AND a.log_time<"blah3"
    ORDER BY a.log_time ASC;

    I get my expected results of parent and children and the codes are deciphered, is there a way of joining c to b before joining b to a to reduce the amount of lines in the select statement and work on my part (there could be lots of child devices),
    also is there a way of having unknown children for a parent device and having the columns automatically appended dependent on the amount matched by log_time and parent_serial.

    Thanks for looking.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Did you use PostgreSQL: Documentation: 9.1: EXPLAIN to determine the order of the JOIN operations? I'd expect that PostgreSQL did exactly what you want it to without any effort on your part.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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