Adding fields to a complex SQL select statement

Question

I need to return the values of two more fields from the "sys_acl_matrix" table, but I don't know how to read these AS and LEFT JOIN things.

The fields I need to add are:

AllowedCount
AllowedPeriodLen

Here is the Query

$GLOBALS['MySQL']->getAllWithKey(
"SELECT `ta`.`ID` 
AS `id`, `ta`.`Name`
AS `title` 
FROM `sys_acl_actions` 
AS `ta` 
LEFT JOIN `sys_acl_matrix` 
AS `tm` 
ON `ta`.`ID`=`tm`.`IDAction` 
LEFT JOIN `sys_acl_levels` 
AS `tl` 
ON `tm`.`IDLevel`=`tl`.`ID` 
WHERE `tl`.`ID`='" . $iMembId . "' 
ORDER BY `ta`.`Name`", "id");

It would also be good if someone could help me understand what this query is doing. I'm lost when it comes to left joins.

Answer 1

That is hard to read because poorly formatted, but:

$GLOBALS['MySQL']->getAllWithKey(
    "SELECT `ta`.`ID`   AS `id`,
            `ta`.`Name` AS `title`,
            `tm`.`AllowedCount`,
            `tm`.`AllowedPeriodLen`
       FROM `sys_acl_actions` AS `ta` 
  LEFT JOIN `sys_acl_matrix` AS `tm` ON `ta`.`ID` = `tm`.`IDAction` 
  LEFT JOIN `sys_acl_levels` AS `tl` ON `tm`.`IDLevel` = `tl`.`ID` 
      WHERE `tl`.`ID`='" . $iMembId . "' 
      ORDER BY `ta`.`Name`", "id");

I would probably do away with all the backquotes, and make sure that the schema was also case-insensitive, but I've kept them for consistency with the question.

You also have an SQL injection possibility with the use of $iMembId in the WHERE clause - if the users supplied the data in the variable, you must sanitize it before adding it to your SQL (Remember Little Bobby Tables!). Ideally, you'd use a placeholder (usually a question mark) and provide the value of $iMembId as the associated value when you execute the SQL.

Without those backquotes, it becomes:

$GLOBALS['MySQL']->getAllWithKey(
    "SELECT ta.ID   AS id,
            ta.Name AS title,
            tm.AllowedCount,
            tm.AllowedPeriodLen
       FROM sys_acl_actions AS ta 
  LEFT JOIN sys_acl_matrix  AS tm ON ta.ID      = tm.IDAction 
  LEFT JOIN sys_acl_levels  AS tl ON tm.IDLevel = tl.ID 
      WHERE tl.ID = '" . $iMembId . "' 
      ORDER BY ta.Name", "id");

Note, though, that the quotes preserve the case of the identifiers inside, so you would probably have to modify your schema before the quoteless version works.

Answer 2

Left join usually returns the records in the left side table and records in the rightside table if there is any which is associated to the left table.But the records in left side will be returned anyway.

Your query will result the records in sys_acl_matrix table.I don't see your query returning any other columns from other 2 tables.You are only returning 2 columns from sys_acl_actions table.

more info : http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

Answer 3

try

$GLOBALS['MySQL']->getAllWithKey(
"SELECT `ta`.`ID` 
AS `id`, `ta`.`Name`
AS `title`, 
`tm`.`AllowedCount`, `tm`.`AllowedPeriodLen` 
FROM `sys_acl_actions` 
AS `ta` 
LEFT JOIN `sys_acl_matrix` 
AS `tm` 
ON `ta`.`ID`=`tm`.`IDAction` 
LEFT JOIN `sys_acl_levels` 
AS `tl` 
ON `tm`.`IDLevel`=`tl`.`ID` 
WHERE `tl`.`ID`='" . $iMembId . "' 
ORDER BY `ta`.`Name`", "id");