ms-access simplying SQL code

Question

is this good code? can it be simplified somehow?

    SELECT u.id,u.title,u.title,u.first,u.last FROM 
  (((tblusers u LEFT JOIN tbluserstudentteacher 
     ON u.id = tbluserstudentteacher.student_teacher_user_id) 
     LEFT JOIN tblUsersSubjects ON u.id = tblUsersSubjects.user_id) 
     LEFT JOIN tblUserAvailability ON u.id=tblUserAvailability.user_id) 
     LEFT JOIN chavrusas ON u.id=chavrusas.luser_id 
       WHERE 1=1 AND (u.gender) LIKE 'm*' 
       AND (chavrusas.luser_type)='shliach' 
       AND (chavrusas.ruser_type)='shliach' AND (u.last LIKE 'd*') 
 GROUP BY u.id, u.title, u.title, u.first, u.last 
 ORDER BY last;

Answer 1

You are doing this the wrong way. Look at all the answers already provided.

Jump into the code.

Make changes as required to load only the data required, and only when required.

Learn why group bys can hurt if not required, and how DISTINCTS can help.

How can YOU simplify the query?

Answer 2

It looks like something generated from the Access Query-Builder interface. It can be simplified, but I think you should first understand where the code is used and what it is trying to achieve before modifying it.

Also, unless it is consuming a LOT of resources, you really do not want to start prematurely optimizing things.

Answer 3

From my answer to your other question, where I cleaned it up as well. See that post for discussion of why.

SELECT u.id, u.title, u.first, u.last
  FROM (tblusers u LEFT JOIN chavrusas c ON u.id = c.luser_id
  AND u.gender LIKE 'm*'
  AND u.last LIKE 'd*'
  AND c.luser_type = 'shliach'
  AND c.ruser_type = c.luser_type)
ORDER BY last;

Answer 4

Abrashka, there are a few things you can look into to optimize the SQL code above. If possible I would recommend denormalizing the table schema of the tables involved to help reduce the number of joins being performed, as well as getting rid of that unnecessary group by clause. LIKE statements are performance hits as well, so you may want to reconsider using those as well.

Answer 5

Doesn't look too bad to me. Some suggestions:

• Pretty formatting e.g. right-align the query keywords to create a 'pipe' and indent the ON clause on a new line.
• Use the AS keyword before a correlation name.
• Use the DISTINCT keyword rather than grouping on the entire SELECT clause (better conveys the intent).
• Move the 'filtering' predicates out of the join and into a WHERE clause.
• Remove the parens.
• Remove the 1=1 predicate and use a parameterized PROCEDURE rather than dynamic SQL ;)

Something like this:

SELECT DISTINCT u.id, u.title, u.first, u.last
  FROM tblusers AS u 
       LEFT OUTER JOIN chavrusas AS c 
          ON u.id = c.luser_id
             AND c.ruser_type = c.luser_type
 WHERE u.gender LIKE 'm*'
       AND u.last LIKE 'd*'
       AND c.luser_type = 'shliach'
 ORDER 
    BY last;

Also review your SQL DDL. Does a user have a 'gender' (masculine, feminine, neuter, etc) or sex (ISO 5218)?