MYsql use same field as joined table

Question

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 cannot 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?

If you explained more... maybe you don't have to add an other redudant table that could indeed create inconsistence... which status do you care about?? i you care about mytable status then put where table1.id=mytable.id and mytable.status=the_status_you_want — Melsi

Chris Baker Chris Baker · Accepted Answer · 2011-09-30T23:08:17

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.

MYsql use same field as joined table

2 Answers