I have a db_select union of two tables where some of the columns are different. I would like to select a column that is only in one of the tables. The standard SQL way of doing this seems to set the column as null for the table where the field missing. Is there a way of doing this with db_select? db_query?
Standard SQL way:
select id, name, date, null as userid, 'A' as recordtype from table1 union all select id, name, null /*as date*/, userid, 'B' as recordtype from table2
What I'm trying to do:
$table_one = db_select('t_one', 'to')->fields('to', array('ID', 'NAME')); $table_two = db_select('t_two', 'tw')->fields('tw', array('ID', 'NAME', 'new_field_not_in_first_table')); $s_query = Database::getConnection() ->select($table_one->union($table_two), 't') ->fields('t', array('ID', 'NAME', 'new_field_not_in_first_table'));
I tried adding
'NULL as new_field_not_in_first_table' in the first table but no luck.
Thanks for your time!