Query help: Select from multiple unrelated tables?

Discussion in 'Databases' started by 123GoToAndPlay, May 15, 2009.

0
  1. #1
    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?
     
    123GoToAndPlay, May 15, 2009 IP
  2. freelistfool

    freelistfool Peon

    Messages:
    1,801
    Likes Received:
    101
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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, May 15, 2009 IP
  3. 123GoToAndPlay

    123GoToAndPlay Peon

    Messages:
    669
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    @freelistfool,

    tx, so that would be "the" way. No other way to combine tables with diff. nr column ??

    regards,
     
    123GoToAndPlay, May 15, 2009 IP
  4. freelistfool

    freelistfool Peon

    Messages:
    1,801
    Likes Received:
    101
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    freelistfool, May 17, 2009 IP
  5. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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.
     
    chisara, May 19, 2009 IP