JOINing four tables together I have four tables, categories, category_permissions, category_relation, and documents.

It's a part of a documentation project I'm working on, just to learn more about relational databases.

Pseudo data for the example tables:

Categories

id | name             
-----------------------
 1 | Default Category 


Category Permissions

category_id | permission_id 
-----------------------------
1           | 1
1           | 2


Category Relation

document_id | category_id 
---------------------------
1           | 1
2           | 1


Documents

id | title            | content
--------------------------------------
1  | Sample Document  | Content!!!!!
2  | Another Document | Hello, World!


What I'm trying to get is rows returned containing information using all of the tables combined. Right now I can get either all of the information surrounding the document (document table, joined with the relation and category tables) or only information surrounding the permissions.

I suspect the issue is because the category permissions aren't directly related to the documents that this is happening.

Something like this is what I'm expecting:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | 1
1  | Sample Document  | Content!!!!!! | 1           | 2
2  | Another Document | Hello, world! | 1           | 1
2  | Another Document | Hello, world! | 1           | 2


But I'm getting more like this:

id | title            | content       | category_id | permission_id
--------------------------------------------------------------------
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
1  | Sample Document  | Content!!!!!! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)
2  | Another Document | Hello, world! | 1           | (NULL)


Here's what I think the query should be, and like I mentioned it works how I expect except the permission id's return null.

SELECT d.id, d.title, d.content, c.id, cp.permission_id
FROM documents d
LEFT JOIN category_relation cr ON cr.document_id = d.id
LEFT JOIN category_permissions cp ON cp.category_id = cr.category_id
LEFT JOIN categories c ON c.id = cr.category_id;


If I switch the second LEFT JOIN category_permissions to a RIGHT JOIN, that is when the data flips and I get the permissions, but not the documents.

My question is, am I structuring my data wrong, or lacking the know-how in MySQL to pull this off? I could just pull all of the documents and then loop through them to query for their categories and permissions, but putting queries in loops has never worked out for me performance-wise, especially when trying it against larger sets of data.
If the structure is not giving you any other grief, then you could just "tighten" the query a bit, in order to get the result that you want.

Using the following test table / data:

create table categories
as select 1, 'Default Category' from dual ;

create table categorypermissions
as
select 1 category_id, 1 permission_id from dual
union select 1, 2 from dual;

create table categoryrelation
as
select 1 document_id, 1 category_id from dual
union select 2, 1 from dual;

create table documents
as 
select 1 id, 'Sample Document' title, 'Content!!!!!' content from dual
union select 2, 'Another Document', 'Hello, World!' from dual;


This query seems to be doing the trick:

select
  D.id
, D.title
, D.content
, CR.category_id
, CP.permission_id
from documents D
  join categoryrelation CR on D.id = CR.document_id
  join categorypermissions CP on CP.category_id = CR.category_id 
order by D.id, CP.permission_id
; 

-- output:
+----+------------------+---------------+-------------+---------------+
| id | title            | content       | category_id | permission_id |
+----+------------------+---------------+-------------+---------------+
|  1 | Sample Document  | Content!!!!!  |           1 |             1 |
|  1 | Sample Document  | Content!!!!!  |           1 |             2 |
|  2 | Another Document | Hello, World! |           1 |             1 |
|  2 | Another Document | Hello, World! |           1 |             2 |
+----+------------------+---------------+-------------+---------------+
4 rows in set (0.00 sec)


(MySQL 5.7)

Dbfiddle here.