使用连接语法的动态MySQL记录集查询

Question

I can't get the JOIN syntax correct to alter this existing MySQL query in PHP to include a join from another table. I have another table specified as DB_TABLE2 that contains columns InvmNumr and InvmDesc. The InvmNumr and InvlNumr are the exact same value in each table and I need to display InvmDesc from Table 2 in this query?

        $res = mysql_query('SELECT LocId, InvlNumr, InvlQuant FROM ' . DB_TABLE1
          . ' WHERE LocId = \'' . mysql_real_escape_string($cType) . '\'
        AND InvlNumr = \'' . mysql_real_escape_string($br) . "'");
        $markup = '';

Answer 1

Read up on LEFT JOIN vs INNER JOIN depending on how your data is stored in your DB_TABLE2 table.

$res = mysql_query('SELECT ' . DB_TABLE1 . '.LocId, ' . DB_TABLE1 . '.InvlNumr, ' .
    DB_TABLE1 . '.InvlQuant, ' . DB_TABLE2 . '.InvmDesc 
    FROM ' . DB_TABLE1 . ' LEFT JOIN ' . DB_TABLE2 . ' ON ' . 
    DB_TABLE1 . '.InvlNumr = ' . DB_TABLE2 . '.InvmNumr 
    WHERE ' . DB_TABLE1 . '.LocId = \'' . mysql_real_escape_string($cType) . '\' 
    AND ' . DB_TABLE1 . '.InvlNumr = \'' . mysql_real_escape_string($br) . "'");