mysql层次递归查询

Question

I’ve been building PHP web pages for quite a while now, but I am not really familiar with MySQL databases. I have thoroughly searched for solutions to problems like mine but without any luck (though there were similar questions answered, I could not figure out how to implement the solution provided).

I have 5 tables in a 3-level hierarchical structure as follows:

Clients (id, name, ...)
 ↳ Projects (id, client_id, name, ...)
   ↳ Contacts (id, project_id, name, ...)
   ↳ Files (id, project_id, name, ...)
   ↳ Events (id, project_id, name, ...)

I am trying to achieve a recursive query with a twist: if I provide an ID of any level (with specifying the level that ID belongs to), it should return that levels both parent and child records in a tree-structure.

For example : I have a Project’s ID, so I would like to return the Client (parent) that Project belongs to, and also the Project’s Contacts, Files, and Events (children). Or if I have an Event’s ID, then I would like to output the Project (parent), the Client (parent parent), plus Contacts and Files (which are on the same level as Events).

The point would be to see a selected item's full history. Is this even possible with one query...? Any suggestions are welcome and appreciated!

Answer 1

A query like this will return the entire data set:

SELECT columns
     , I
     , actually
     , want
   FROM clients c
  LEFT
  JOIN projects p
    ON p.fk = c.pk
  LEFT 
  JOIN 
     ( SELECT 'contact' type, common, columns FROM contacts
        UNION
       SELECT 'file', etc
       UNION etc
     ) x
    ON x.fk = p.pk;

You can easily attach a WHERE clause to this or, if the data set is not so large, just handle the filtering in JavaScript or similar. Note that your current design has potential for tremendous redundancy!