Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    3

    Unanswered: Querying multiple tables

    This seems simple, but I've been stymied for a couple days. I have three
    tables with news articles. The tables themselves are structured identically.
    Each article in these tables has a date.

    I want to pull the 10 latest items (by date) from the 3 tables. I only want
    10 items, and I want the latest ones (regardless of which table they came
    from).

    SELECT * FROM table1, table2, table3 ORDER BY date ASC LIMIT 10

    ...but that query doesn't work.

    Is there another way I can do it... or will I need to resort to pulling the
    10 latest from each of the 3 tables and then comparing them in PHP?

  2. #2
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    I never try this in mysql but in ms sql u can use union eg.
    select * from table1 union
    select * from table2 union
    select * from table3 limit 10

    take note : u should have the same fields in ur tables.

  3. #3
    Join Date
    Oct 2005
    Posts
    3
    Quote Originally Posted by popskie
    I never try this in mysql but in ms sql u can use union eg.
    select * from table1 union
    select * from table2 union
    select * from table3 limit 10

    take note : u should have the same fields in ur tables.
    Hmmm... do you know where would the ORDER BY directive fit in?

  4. #4
    Join Date
    Oct 2005
    Posts
    3
    Ah... got it.

    select * from table1 union
    select * from table2 union
    select * from table3
    order by date desc limit 10

    ...perfect. Thanks so much!

  5. #5
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    select * from table1 union
    select * from table2 union
    select * from table3 order by fieldname asc limit 10

    Ok i mention earlier that i never use this in mysql only the union fucntion. Sory for not mentioning that one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •