SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by date from 2 different tables

    I'm trying to figure out how to output data from 2 different db tables depending on the release dates...

    Table1:
    id, subject, content, date
    Table1:
    id, nsubject, ncontent, ndate

    How do I get both into the query and then print in order of the dates...

    Thanks in advance :-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id, subject, content, `date`
      FROM Table1
    UNION ALL
    SELECT id, nsubject, ncontent, ndate
      FROM Table2
    ORDER
        BY `date`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But what if the 2 fields don't have the same amount of fields... This is the full script I want to work

    Currently working:
    PHP Code:
    $sql="SELECT u.fname, u.lname, u.new_userid, n.news, n.clubid, n.newsid, cl.clubname FROM ".$prefix."_club_users AS cu
            INNER JOIN "
    .$prefix."_clubnews AS n ON cu.clubid = n.clubid
            INNER JOIN "
    .$prefix."_users u ON u.new_userid = n.userid
            INNER JOIN "
    .$prefix."_club cl ON cl.clubid = cu.clubid
            WHERE cu.new_userid='
    $userid'
            ORDER BY n.dbdate DESC
            LIMIT 5"

    Now this is the table I want to join with the clubnews somehow:
    PHP Code:
    $sql="SELECT lang, subject, content FROM ".$prefix."_news WHERE lang=$lang ORDER BY date DESC"
    Can this be done?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    But what if the 2 fields don't have the same amount of fields...
    then you should not "dumb down" your original question to make it look as though the tables have the same number of similar columns

    Quote Originally Posted by jmansa View Post
    Now this is the table I want to join with the clubnews somehow:
    you'll have to explain how the tables should be joined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    then you should not "dumb down" your original question to make it look as though the tables have the same number of similar columns
    Sorry... My bad.

    Quote Originally Posted by r937 View Post
    you'll have to explain how the tables should be joined
    I the "clubnews" I have 2 fields (news, dbdate) which is the ones I use. In the "news" table I have (subject, content, date) which is the fields of important allthough I have a "lang" filed which determens the language of the news... I want to join those 2 tables and order the news from them both by date/dbdate.

    Hope this helps...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    when you say you want to "join" these two tables, are you ~sure~ youe want to match the rows from them on some column?

    or are you really looking to do a UNION?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I might want to be looking at UNION but not sure...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, that's fair

    perhaps you could go and do a bit of research on what UNION does?

    after that, you might be in a better position to describe what you want to do with your queries and tables...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    perhaps you could go and do a bit of research on what UNION does?
    OK... Have been surfing around, and this is what I have come up with...
    PHP Code:
    $sql="SELECT n.newsid, n.news, n.dbdate FROM ".$prefix."_clubnews n
                        UNION
                      SELECT gn.id, gn.content, gn.date FROM "
    .$prefix."_GA_news gn WHERE gn.lang=213
                          ORDER BY n.dbdate DESC LIMIT 5"

    If I don't user the n. or gn. it works fine, but how come I cant use those???

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    ... but how come I cant use those???
    because the result set of a UNION query has unqualified column names

    the column names are assigned in the first SELECT

    so in your php code, you would reference newsid, news, and dbdate

    alternatively, you can assign column aliases...
    Code:
    SELECT n.newsid AS the_id
         , n.news   AS the_content
         , n.dbdate AS the_date
      FROM ".$prefix."_clubnews n
    UNION ALL
    SELECT gn.id
         , gn.content
         , gn.date 
      FROM ".$prefix."_GA_news gn 
     WHERE gn.lang=213
    ORDER 
        BY the_date DESC LIMIT 5
    and then reference the_id, the_content, and the_date

    but one thing still puzzles me

    in post #3, those two queries return wildly different sets of columns, that really aren't very amenable to a UNION (unlike post #1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •