Thread: Join before the join
07-03-14, 07:27 #1Registered User
- 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,ch1_code.name,ch2.s erial,ch2_code.name,ch3.serial,ch3_code.name
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.
07-03-14, 09:55 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
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.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.