What is the principle behind the complicated join join?

suppose 4 tables

select * from t1, t4 left join t2 on t2.id = t1.t2_id left join t3 on t3.id = t1.t3_id where t1.name = 12 and t2.parent = 3 and t4.id = 100

accordingly, this is not a working scl.

the examples in the dock are very simple http://www.postgresql.org/docs/9.3/static/queries-table-expressions.html and do not give understanding

those. what is the correct syntax where to drive the left join? In the end? in the middle?

  • Try to write more detailed questions. What exactly do you see the problem and what do you want to see in the answer? - Kromster
  • one
    I don’t understand at all what should be answered - etki
  • Find a book undershanding sql or понимание sql . True, I don’t know if there is a join , it wasn’t there before. But the book is useful especially for noobs. - Sergey

2 answers 2

In PostgreSQL, the comma operator does not work completely like the JOIN operator, and using LEFT JOIN after using a comma carries a lot of restrictions. For example, in the above query in LEFT, you could refer to t4 , but not t1 . In principle, in a particular case _t4_, _t1_ permutation _t4_, _t1_ instead of t1 ,t4 should help. But in general, if you want to use at least one JOIN in the query, you should stop using the comma :

 select * from t1 CROSS join t4 left join t2 on t2.id = t1.t2_id left join t3 on t3.id = t1.t3_id where t1.name = 12 and t2.parent = 3 and t4.id = 100

In addition, since you do not have conditions for joining tables t1 and t4 (you multiply their contents completely), then CROSS JOIN should be used instead of the usual JOIN .

    if not correct then correct. the idea is like

     SELECT * FROM ( SELECT * FROM t1, t4 WHERE t4.id = 100 AND t1.id = t4.id ) t1 LEFT JOIN (SELECT * FROM t2 WHERE t2.parent = 3) t2 ON t2.id = t1.t2_id LEFT JOIN (SELECT * FROM t3 WHERE t3.parent = 3) t3 ON t3.id = t1.t3_id

    those. joins as modules connect to main request

    • This is not the answer, but part of the question, or rather your idea of ​​what is happening. Transfer to the question. And state what your question is, what exactly is the problem - Kromster
    • what is the correct syntax here is the question. and given a possible option as an answer - des1roer
    • And what, if the syntax is wrong, is it not executed and returns an error? - Kromster
    • my query is a bit trickier gist.github.com/des1roer/4e0c4cc9a86d2be67c6eebf83eb42e2c and yes it doesn't work in this form - des1roer
    • Great, we get to the original problem. Why doesn’t it work, what does it complain about, what errors does it display? Try to simplify the query and find out what the error is, and then ask about it here. - Kromster