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