MySQL select from multiple tables

Discussion in 'Web Design and Development' started by ezkimo, May 24, 2010.

  1. ezkimo macrumors regular

    Joined:
    Sep 12, 2002
    #1
    Hi,
    I'm having some trouble figuring out how to do this, it seems like it should be easier than I'm making it.

    I have a bunch of tables that are identical in structure and all have a field called post_date (formatted 2010-05-24 15:42:25) which is the date the row was entered and an auto-incremented ID, but each table has a different numbers of entries.

    I want to be able to select the the newest 10 entries from all the tables by post_date. So that could mean the results are returned as a row from table 1, then from table 4, then from table 1 again, then table 8, etc etc because it's based on post date.

    Any idea how to go about this?!

    Thanks!
     
  2. stomer macrumors 6502a

    Joined:
    Apr 2, 2007
    Location:
    Leeds, UK
    #2
    Code:
    Select * from (
    select * from table1
    order by post_date
    limit 10
    union 
    select * from table2
    order by post_date
    limit 10
    union 
    select * from table3
    order by post_date
    limit 10
    union
    select * from table4
    order by post_date
    limit 10
    ) order by post_date limit 10
    
    Something like that?
     
  3. ezkimo thread starter macrumors regular

    Joined:
    Sep 12, 2002
    #3
    Thank you! Not sure why I was making it so complicated.

    I ended up using this because I was getting some mysql errors:

    Code:
    SELECT * FROM (
    (SELECT * FROM 14_posts 
    ORDER BY ID DESC
    LIMIT 10)
    UNION 
    (SELECT * FROM 15_posts 
    ORDER BY ID DESC
    LIMIT 10)
    UNION 
    (SELECT * FROM 16_posts 
    ORDER BY ID DESC
    LIMIT 10)
    UNION
    (SELECT * FROM 17_posts 
    ORDER BY ID DESC
    LIMIT 10)
    UNION
    (SELECT * FROM 18_posts
    ORDER BY ID DESC
    LIMIT 10 )
    ) AS Tmp ORDER BY post_date DESC LIMIT 10
    
     

Share This Page