MYsql use same field as joined table

I have site i need to modify without changing anything and only way i can do it is by adding mytable which has status field.

Now i have request like

if...{
   $filter = "status = 0";
}

SELECT first_name, last_name, position, wage 
FROM table1, mytable
WHERE table1.id = mytable.id 
$filter 

Problem is that both "table1" and "mytable" have statuses and i ca开发者_如何学Pythonnnot do anything about this because this filter used also for 16 other requests looking "exactly similar" except they use instead table1 - table2, table3, table4, table5, table6,... etc and status used only for filtering can someone help?

Is in MySQL something like $this in php class so it knows i reference to table in FROM field so i could use JOIN LEFT instead of specifying table in FROM?

You can (and probably should, for clarity) prefix any column with its table name. You may do so both in the SELECT portion of the query as well as the WHERE portion. For example:

SELECT 
    table1.first_name, 
    table1.last_name, 
    mytable.position, 
    mytable.wage 
FROM 
    table1, 
    mytable 
WHERE 
    table1.id = mytable.id AND 
    table1.status = "0"

If you are going to be dynamically including the tables and want to keep the filter code generic, you can use the AS keyword to create aliases, so:

$use_table = 'table1';
$sql = '
    SELECT 
        filter_table.first_name, 
        filter_table.last_name, 
        mytable.position, 
        mytable.wage 
    FROM 
        '.$use_table'. AS filter_table, 
        mytable 
    WHERE 
        filter_table.id = mytable.id AND 
        filter_table.status = "0"
';

... that way, you are able to switch which table you are using in $use_table without changing any of the other SQL.

You can specify to witch table's column you reference, like "table1"."status" :

SELECT first_name, last_name, position, wage 
FROM table1, mytable
WHERE table1.id = mytable.id AND "table1"."status" = 0