SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    UK
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select across similar tables

    maybe because it's monday, but i'm banging my head against the wall trying to get this to work.

    I've got 5 tables, each table has a timestamp field (called datetime) and a message field, all i want to do is pull out the last entry that was added from all the tables put together.

    I thought something like this would work:
    Code:
    SELECT date_format(datetime, '%D %M %Y  %H:%i') as datetime,message FROM (table1,table2,table3,table4,table5,) ORDER BY datetime DESC LIMIT 1
    but it doesn't, it generates this error "Column: 'datetime' in field list is ambiguous", i'm guessing maybe joins are needed but i'm not quite sure how to go about it.

    If anyone's got any examples it would be greatly appreciated.
    Thanks

    SE

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    SELECT date_format(datetime, '%D %M %Y %H:%i') as datetime
    , message FROM table1 LIMIT 1
    UNION ALL
    SELECT date_format(datetime, '%D %M %Y %H:%i')
    , message FROM table2 LIMIT 1
    UNION ALL
    SELECT date_format(datetime, '%D %M %Y %H:%i')
    , message FROM table3 LIMIT 1
    UNION ALL
    SELECT date_format(datetime, '%D %M %Y %H:%i')
    , message FROM table4 LIMIT 1
    UNION ALL
    SELECT date_format(datetime, '%D %M %Y %H:%i')
    , message FROM table5 LIMIT 1
    ORDER BY 1 desc

    if you are not on mysql 4, you will have to issue 5 separate queries

    tip: do not name your column using a reserved word

    tip: do not sort by character strings dd/mm/yy, you will get 1, 11, 12, ... 19, 2, 21, ...


    rudy
    http://r937.com/

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    UK
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, cheers, i'll give that a go - thanks for the tips


Bookmarks

Posting Permissions

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