Select all possible rows even if value doesn't exist on JOIN-CodePudding

I'm trying to create a permission system but I'm not able to select all the possible options I have on the database. For example, considering I have the following tables on my database:

tb_group
id | group | slug
1 | Admin | admin
2 | Manager | manager
3 | Store Manager | store-manager
4 | Secretary | secretary

tb_area
id | area
1 | products
2 | users
3 | categories
4 | purchases

tb_permission
id_store | id_group | id_area | access | add | edit | remove
2        | 2        | 1       | 1      | 0   | 0    | 0
2        | 2        | 3       | 1      | 1   | 1    | 0

The expected result I want is to get all the possible permissions (access, add, edit, remove) for all possible areas from an specific group. So I would have this end result:

id_store | id_group | area       | access | add | edit | remove
2        | 2        | products   | 1      | 0   | 0    | 0
2        | 2        | users      | 0      | 0   | 0    | 0
2        | 2        | categories | 1      | 1   | 1    | 0
2        | 2        | purchases  | 0      | 0   | 0    | 0

So, even if I don't have any registers on table tb_permission for group 2 and areas 2 and 4, I want to have them on the query result with null or 0 values.

This is the code I have currently, but it only displays the result if the area exists on the tb_permission table.

SELECT
  tp.access, tp.add, tp.edit, tp.remove,
  ta.area,
  tg.slug

FROM tb_permission tp

LEFT JOIN tb_area ta
  ON ta.id = tp.id_area

LEFT JOIN tb_group tg
  ON tg.id = tp.id_group

WHERE tp.id_store = 2

So I want to get all permissions the group number 2 has and also all the ones this group still has no register on the database. But my query is only returning the ones that is already on the database. Is it possible to get the result I expect with a single query?

CodePudding user response：

You need to LEFT JOIN with tp_permission. Put the condition of tp.id_store = 2 in the ON condition so it doesn't filter out the non-matching rows.

You can CROSS JOIN the group and area tables to get all combinations.

SELECT 2 AS id_store, tg.id AS id_group, ta.area,
        IFNULL(tp.access, 0) AS access, 
        IFNULL(tp.add, 0) AS add,
        IFNULL(tp.edit, 0) AS edit, 
        IFNULL(tp.remove, 0) AS remove
FROM tb_group AS tg
CROSS JOIN tb_area AS ta
LEFT JOIN tb_permission AS tp
    ON ta.id = tp.id_area AND tg.id = tp.id_group AND tp.id_store = 2
WHERE tg.id = 2