Laszlo Laszlo - 1 year ago 168
MySQL Question

mysql hierarchial recursive query

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 Source

A query like this will return the entire data set:

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

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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download