هل يوجد استعلام SQL موجود لاستخراج الأذونات المميزة لجميع الفئات الفرعية لفئة رئيسية مقارنة بالأذونات لتلك الفئة الرئيسية، باستخدام إضافة مستكشف البيانات؟
This SQL shows all permissions in subcategories that are not in the parent categories.
SELECT subcategories.* FROM (
SELECT category.parent_category_id
, category.id category_id
, category.name category_name
, category.position category_position
, category_group.permission_type permission_type
, groups.name group_name
, groups.id group_id
FROM categories category
INNER JOIN category_groups category_group
ON category_group.category_id = category.id
INNER JOIN GROUPS
ON groups.id = category_group.group_id
WHERE parent_category_id IS NOT NULL
) subcategories
LEFT JOIN (
SELECT category.id category_id
, category.name category_name
, category.position category_position
, category_group.permission_type permission_type
, groups.name group_name
, groups.id group_id
FROM categories category
INNER JOIN category_groups category_group
ON category_group.category_id = category.id
INNER JOIN GROUPS
ON groups.id = category_group.group_id
WHERE parent_category_id IS NULL
) categories
ON categories.category_id = subcategories.parent_category_id
AND categories.group_id = subcategories.group_id
AND categories.permission_type = subcategories.permission_type
WHERE categories.category_id IS NULL
Example results
"parent_category_id","category_id","category_name","category_position","permission_type","group_name","group_id"
70,40,"CSMU Bogan's Run",57,1,"bd2l",44
70,40,"CSMU Bogan's Run",57,1,"bd2l-sales-marketing",59
70,55,"CSMU Miaffirmations",63,1,"miaffirmations",56
70,45,"CSMU Music",61,1,"csmu-music",64
إعجابَين (2)