SQL Select * from multiple tables

Question

Using PHP/PDO/MySQL is it possible to use a wildcard for the columns when a select is done on multiple tables and the returned array keys are fully qualified to avoid column name clash?

example:

SELECT * from table1, table2;

gives:

Array keys are 'table1.id', 'table2.id', 'table1.name' etc.

I tried "SELECT table1.*,table2.* ..." but the returned array keys were not fully qualified so columns with the same name clashed and were overwritten.

Answer 1

you can do this:

SELECT Table1.*,Table2.xyz, Table2.abc,... From...

where you get all columns from one table using "*" and then just the columns from the other table you need, so there is no clash.

You could also use column aliases, where you "rename" a column:

SELECT Table1.A AS T1_A,Table2.A AS T2_A,... From...

your result set would be of columns T1_A and T2_A

Answer 2

Unfortunately, PHP (particularly the MySQL, PgSQL, MSSQL extensions) will always have your columns overwrite in the case of overlap.

I would recommend creating a View in your database, and Alias your columns so that they are "fully-qualified".

For example: (MySQL)

CREATE VIEW viewTable1Table2 AS
    SELECT
        t1.field1 AS Table1Field1
        t2.field1 AS Table2Field1
    FROM Table1 t1
        INNER JOIN Table2 t2
            ON t1.id = t2.id;

The syntax may not be perfect, but you can get a general idea of what I am talking about.

Answer 3

Unfortunately, no; there is no SQL syntax for ensuring that column names are unique.

If you truly don't know the names of the columns and must use SELECT *, your only real option would be to revert to some very ugly looking dynamic SQL that could inspect the structure of the tables and generate a query that would select them all explicitly with a table-name prefix.

I don't know which RDBMS you're using, but something like this should work on SQL Server:

declare @columns table (idx int identity(1,1), tablename varchar(100), columnname varchar(100))

insert into @columns (tablename, columnname) 
select tablename, columnname

from INFORMATION_SCHEMA.COLUMNS

where tablename in ('table_1', 'table_2')

declare @sql nvarchar(4000)

declare @i int
declare @cnt in

declare @col varchar(100)
declare @table varchar(100)

select @i = 0, @cnt = max(idx), @sql = '' from @columns

while @i < @cnt
begin
    select @i = @i + 1

    select @col = columnname, @table = tablename from @columns where idx = @i

    if len(@sql) > 0
        select @sql = @sql + ', '

    select @sql = @sql + '[' + @table + '].[' + @col + '] as [' + @table + '_' + @col + ']'
end

select @sql = 'select ' + @sql + ' from table_1, table_2'

exec sp_executesql @sql

Answer 4

Yes, you can.

$stmt->execute();
$qualifiedColumnNames = array();
for ($i = 0; $i < $stmt->columnCount(); $i++) {
    $columnMeta = $stmt->getColumnMeta($i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

print_r(array_combine($qualifiedColumnNames, $stmt->fetch(PDO::FETCH_NUM)));

mysql and mysqli extensions also have these capabilities.