Hi, I am looking for the query to select from multiple unrelated tables with different nr of columns $sql = "(SELECT * FROM `table1`) UNION (SELECT * FROM `table2`) ORDER BY RAND()"; Code (markup): works only when i have 2 tables with equal number of columns. Any tips?
You must have the same number of columns in each select when you use UNION, INTERSECT, etc. clauses. So as a work around you can add dummy columns to the tables that have less columns. Select * from 3_column_table union Select *, 1 from 2_column_table union Select *, 1, 2 from 1_column_table Code (markup):
@freelistfool, tx, so that would be "the" way. No other way to combine tables with diff. nr column ?? regards,
Heh, don't know if it is "the" way, but it's how I've always done it. Just put something in the dummy columns that you don't expect to find in the data set. That way your program code can ignore your dummy data value when it comes across it.
Instead of the * you can use named columns using "as" (SELECT name as name, surname as surname FROM names) UNION (SELECT myname as name, birthname as surname FROM mynames) By using * you are making the order of the columns in the different tables depend on each other, by specifying names you don't. The dummy columns can then be used to properly combine different colum counts. It is more efficient to only query the columns you need.