Advanced Query

Enfernikus · 06-29-2009, 03:11 PM

I currently have a system which employs cascading user levels there is first the Group User levels and their associated rules than there are the User Specific "rules" with the ladder over riding the former.

Currently I'm employing 2 queries to check for the User and exit out or the Group, how ever I was wondering if it could be done in one.

Currently employed queries

sql Code:

SELECT DISTINCT ea_acl_options.`handler`, 
         ea_acl_options.`page`, 
         ea_acl_options.`limit`,  
         ea_acl_options.isgroup,
         ea_acl_options.`allowed`,  
         ea_acl_options.`guid` AS gid,  
         ea_user_levels.sort
FROM ea_acl_options 
JOIN ea_user_belongs
LEFT JOIN ea_user_levels ON ea_user_levels.id = ea_user_belongs.g_id
WHERE guid=2 OR guid=0 AND isgroup=0

sql Code:

SELECT DISTINCT ea_acl_options.`handler`, 
          ea_acl_options.`page`, 
          ea_acl_options.`limit`,
          ea_acl_options.`isgroup`,
          ea_acl_options.`allowed`,
          ea_acl_options.`guid` AS gid,  
          ea_user_levels.sort
FROM ea_acl_options 
JOIN ea_user_belongs ON ea_acl_options.guid = ea_user_belongs.g_id
JOIN ea_user_levels ON ea_user_belongs.g_id = ea_user_levels.id 
WHERE ea_user_belongs.u_id = 2 AND ea_acl_options.isgroup = 1

It differs by the last three lines. I might need to rethink my database design...

Perchance could anyone tell me what I could do? I was thinking of using the UNION statement but then there are possibilities of a group and a user having the same id.

CoryMathews · 06-30-2009, 02:18 PM

I think you need to change the design of your database. Take a step back and ask yourself.

What tables can I merge and what tables can I get rid of?

If you database is no longer 100% 3nf who cares. You should not have to do 2 joins in a single query. You really want to avoid as many joins as possible as they will slow down your querys a greay deal once the database gets some size to it.