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

    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,ch1_code.name,ch2.s erial,ch2_code.name,ch3.serial,ch3_code.name
    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 ch1.code_id=ch1_code.id
    LEFT JOIN c AS ch2_code ON ch2.code_id=ch2_code.id
    LEFT JOIN c AS ch3_code ON ch3.code_id=ch3_code.id
    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
    Location
    In front of the computer
    Posts
    15,579
    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.

    -PatP
    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
  •