How to build a query involving several different tables with different relationships

I'm currently trying to write a query which involves 5 main tables, 2 of which are referring to a 3rd with foreign keys, but not relating to each-other... and one of the first 2 tables is the main subject of the query. Here's a basic synopsis.

instance             user 
--------             ----
id                   id
name                 name
user_id


def                  def_map
---                  ------
id                   id
name                 instance_id
user_id              def_id


def_data
--------
id
name
def_id
user_id

What I want to do is get a list of all of the 'def_map's for a single user. In each row I'd like the associated def_data to be displayed as well. So the rows would be like:

instance.id, def.id, def.name, def_data.name, user.id

I can figure out how to get all info except def_data.name in the result, or all info except for instance.id ... but can't figure out how to get then all together using one query. Is it possible? I think part of the problem is I don't know if there is a special word that describes this type of query so I开发者_StackOverflow社区 would know what to read up on.

I'm using DQL, but examples in SQL would be just as useful. Thanks in advance for any help.

If you can pull the data individually using 2 queries you simply need to UNION them together

    SELECT user.id, i.id, d.id, dd.name 
    FROM user u  
    INNER JOIN instance i ON u.id=i.user_id
    INNER JOIN def d ON dm.user_id = u.id 
    INNER JOIN def_data dd ON dd.def_id = d.id  
    UNION ALL  
    SELECT u.id, i.id AS instance_id, d.id, dd.name  
    FROM instance i 
    INNER JOIN user u ON u.id=i.user_id
    INNER JOIN defmap dm ON dm.instance_id=i.id  
    INNER JOIN def_data dd ON dd.def_id=dm.def_id

select I.id, D.id, D.name, DD.name, U.id
from user U inner join instance  I on I.user_id =  U.id
Inner join def D on D.user_id = U.id
inner join def_map DM on DM.def_id = D.id AND I.id = DM.instance_id
inner join def_data DD on DD.def_id = D.id AND U.id = DD.user_id

Test data:

USER                        
+----+-------------------------+
| id | name                    |
+----+-------------------------+
|  1 | Name1                   |
+----+-------------------------+                
Instance 

+----+------+---------+
| id | name | user_id |
+----+------+---------+
|  1 | I1   |       1 |
+----+------+---------+    
def_map    
+--------+-------------+--------+
|   id   | instance_id | def_id |
+--------+-------------+--------+
|      1 |           1 |  1     |
+--------+-------------+--------+

def
+--------------+------+
| id | name | user_id |
+--------------+------+
|  1 | df1  |      1  |
+--------------+------+

def_data
+--------+------+--------+---------+
|     id | name | def_id | user_id |
+--------+------+--------+---------+
|      1 | dd1  |      1 |       1 |
+--------+------+--------+---------+

Result

+-------------+--------+----------+---------------+---------+
| instance.id | def.id | def.name | def_data.name | user.id |
+-------------+--------+----------+---------------+---------+
|           1 |      1 | df1      | dd1           |       1 |
+-------------+--------+----------+---------------+---------+