Mysql – JOINing four tables together

join;MySQL

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.

Best Answer

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.

Related Solutions

Mysql – Implementation Idea for login structure (login id with two different tables)

First of all, every thing depends on business logic you have. If employees and customers it's fine to use 2 diferrent tables, to connect them with the table WEB_USER_LOGIN I would just append and field like 'usertype'.

If your database is Oracle you can have object tables to solve this problem, if its PostgreSQL you can hava inherited tables.

If its mysql I would use just one table with both fields, but if you prefer to use the left join it's ok but I would create a VIEW for it.

MySQL – Table with Multiple Foreign Keys vs Separate Tables for Each FK

Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines:

CREATE TABLE Category (
    Category_ID           INT NOT NULL AUTO_INCREMENT primary key,
    Parent_Category_ID    INT NOT NULL REFERENCES Category(Category_ID),
    Description           varchar(255) not null
)

This type of structure is not as well supported by MySQL as some other RDBMS's, but these issues can all be worked through.

You will also need a revised BookCategory mapping table such as this:

CREATE TABLE BookCategory (
    Book_ID        INT NOT NULL references Book(Book_ID),
    Category_ID    INT NOT NULL references Category(Category_ID)
)

My apologies if the Foreign Key syntax above is not quite correct for MySQL; I usually work with SQL Server.

Update With this INSERT:

insert Category(Parent_Category_ID,Description)
values (1,'Mystery')
      ,(2,'SciFi')
      ,(2,'Aliens')
      ,(3,'Humanoid')
      ,(3,'Reptilian')
      ,(3,'Other')
;

the following SELECT:

select
    root.Category_ID,
    cast(
      coalesce(grandparent.Description + '-',' ') +
      coalesce(parent.Description + '-',' ') +
            root.Description 
    as varchar(255)) as Description
from Category                           root
left join Category                      parent
   on parent.Category_ID            = root.Parent_Category_ID
  and root.Parent_Category_ID      <> root.Category_ID
left join Category                      grandparent
   on grandparent.Category_ID       = parent.Parent_Category_ID
  and parent.Parent_Category_ID    <> parent.Category_ID

yields this:

Category_ID Description
----------- 
1             Mystery
2             SciFi
3            SciFi-Aliens
4           SciFi-Aliens-Humanoid
5           SciFi-Aliens-Reptilian
6           SciFi-Aliens-Other

When Common Table Expressions are supported (not yet in MySQL I understand) the following will provide automatic nesting of the hierarchy levels:

with data as (
    select
        Category_ID,
        cast(Description as varchar(255)) as Description
    from Category
    where Parent_Category_ID = Category_ID

    union all

    select
        child.Category_ID,
        cast(parent.Description + '-' + child.Description as varchar(255)) as Description
    from data       parent
    join Category   child
       on child.Parent_Category_ID  = parent.Category_ID
    and child.Parent_Category_ID <> child.Category_ID
)
select 
     Category_ID,
     Description
from data

Best Answer

Related Solutions

Mysql – Implementation Idea for login structure (login id with two different tables)

MySQL – Table with Multiple Foreign Keys vs Separate Tables for Each FK

Related Question