People, there is such a task

· A o B § <doc1> § <doc2> o C § <doc3> · D o E § F § <doc5> o <doc4> · G o H § I · <doc6>

This tree in DB. Capital letters are folders. These are documents. It is worth noting that these are 2 different tables.

Table document

  ID NAME P_ID ---------- -------------------- ---------- 1 doc1 2 2 doc2 2 3 doc3 3 4 doc4 4 5 doc5 5 6 doc6 8

Folder table

  ID NAME P_ID ---------- -------------------- ---------- 2 b 1 3 c 1 4 d 5 e 4 6 f 5 7 g 8 h 7 9 i 8

It is necessary with a single SELECT query to select all documents of the 2nd level, that is, the level marked with §. The output should be

 doc1,doc2,doc3,doc5.

Thank you in advance.

IMPORTANT!!! there should be no recursion, except select subqueries

    1 answer 1

    Today, a similar question has already flashed: A selection of elements of level 3 in the MySQL hierarchy A request for a scheme from that topic:

     SELECT ds.document_id , ds.parent_id FROM folder_s l1 inner JOIN folder_s l2 on l1.folder_id = l2.parent_id INNER JOIN document_s ds ON ds.parent_id = l2.folder_id where l1.parent_id = 1

    later requested option via subqueries:

     SELECT ds.document_id , ds.parent_id FROM document_s ds where ds.parent_id IN ( SELECT l2.folder_id FROM folder_s l1 inner JOIN folder_s l2 on l1.folder_id = l2.parent_id where l1.parent_id = 1 )
    • And in a forehead through SELECT in SELECT how to make? - Eugene