SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    union of 4 queries

    how would i union 4 queries together? ie something like

    Code:
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS1
    UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS2
    	UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS3
    	UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS4
    	UNION
    Thanks

    btw i realise the above wont work - its just to illustrate my idea

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by billybrag View Post
    how would i union 4 queries together? ie something like

    Code:
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS1
    UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS2
    	UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS3
    	UNION
    SELECT Title, CAST(PUBLISHEDDATE as date) as publisheddate, rsslink, content
    	FROM RSS4
    	UNION
    Thanks

    btw i realise the above wont work - its just to illustrate my idea
    Except for the last line (get rid of that last UNION after RSS4), it should work just fine

  3. #3
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol - excellent, that was easy

    Ok, to mix it up a bit, is it easy to select the latest from each of the 4 feeds, then combine it?

    so i would end up with 4 results?
    edit - thanks btw

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do a
    Code:
    ORDER BY PUBLISHEDDATE DESC
    LIMIT 1
    on each of the four selects.

  5. #5
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah crud, cant use limit, dammit. This is a coldfusion thing. nothing is easy

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I don't know anything about coldfusion, but why can't you use LIMIT? Aren't you using MySQL?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i really have to ask this: why do you have 4 separate tables? they look like they all have the same kind of data!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its for a "query of queries" thing in coldfusion, so its not in essance 4 tables....

    QofQ uses a subset of mysql in which limit is not included

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

    by the way, coldfusion's query-of-query does not use a "subset of mysql"

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

  10. #10
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, it uses a subset of SQL

    When you query an existing query, you can use a subset of SQL statements, functions, and operators.
    ...from Adobe live docs


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
  •