MySQL Join Query 4 tables

Question

I'm trying to create a query with multiple joins, but it's not working for me. I have the follow query statement:

SELECT DISTINCT s.per_id 
              , s.per_key 
              , s.per_disabled 
              , p.acc_id 
              , a.acc_name 
              , p.approved_acc_id 
              , p.per_time 
           FROM acc_permissions p 
           JOIN svr_permissions s 
           JOIN acc_general a 
           JOIN svr_group_permissions g
          WHERE a.acc_id = p.acc_id 
            AND p.per_id = s.per_id 
             OR a.acc_per_group = g.group_id 
            AND a.acc_id = p.acc_id

Now if you don't want to examine this query, I understand so I will example my table structure;

First Table (includes users):

acc_general

Second table (includes permissions (linked to users)):

acc_permission - This table includes rows that are linked to the acc_id in acc_general. Multiple rows are possible for one unique acc_id in this table.

Third table (includes permissions (liked to groups)):

group_permissions

Now this includes rows that are linked to groups, each group has multiple rows in this table.

Inside acc_general there is a field called; acc_group_id, this is liked with the group_id inside group_permissions

So I need a query that returns all permissions from all players. But it should not create duplicated permissions for a account.

So if I have an account that has a permission id 1 inside acc_permission and it has permission id 1 inside group_permissions it should ignore it.

It's hard to example, but i hope someone understands what I want.

Regards, Roel

Answer 1

join syntax for your query

SELECT DISTINCT s.per_id AS per_id,
       s.per_key AS per_key,
       s.per_disabled AS      per_disabled,
       p.acc_id AS acc_id, 
       a.acc_name as acc_name,
       p.approved_acc_id AS   approved_acc_id,
       p.per_time AS per_time 
from acc_permissions p 
join svr_permissions s 
ON p.per_id = s.per_id
join acc_general a 
ON a.acc_id= p.acc_id 
left join  svr_group_permissions g
ON a.acc_per_group = g.group_id