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 |
+-------------+--------+----------+---------------+---------+