SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    @alexstanford Alex's Avatar
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida
    Posts
    757
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    UNION / INNER JOIN ORDER BY

    Code MySQL:
    (SELECT 
        a.id, a.name, a.enabled, a.fromFile, a.title, a.content,  a.titleURL, a.side 
    FROM !prefix!sidebars a 
    WHERE 
        a.enabled = 1 
    AND 
        a.id NOT IN ( 
            SELECT sidebar FROM !prefix!pages_sidebars WHERE page = :pageId 
        ) 
    ) 
    UNION 
    (SELECT 
        s.id, s.name, s.enabled, s.fromFile, s.title, s.content,  s.titleURL, s.side 
    FROM !prefix!sidebars s 
        INNER JOIN 
            !prefix!pages_sidebars p 
        ON 
            s.id = p.sidebar 
    WHERE p.page = :pageId AND p.enabled = 1)

    The above code works fine. However, on attempt to add an order by clause, it retrieves 10x the amount of data. 10 because of the number of rows in the other table.

    Changed code:

    Code MySQL:
    (SELECT 
        a.id, a.name, a.enabled, a.fromFile, a.title, a.content,  a.titleURL, a.side, b.sortOrder 
    FROM !prefix!sidebars a, !prefix!pages_sidebars b 
    WHERE 
        a.enabled = 1 
    AND 
        a.id NOT IN ( 
            SELECT sidebar FROM !prefix!pages_sidebars WHERE page = :pageId 
        ) 
    ) 
    UNION 
    (SELECT 
        s.id, s.name, s.enabled, s.fromFile, s.title, s.content,  s.titleURL, s.side, p.sortOrder 
    FROM !prefix!sidebars s 
        INNER JOIN 
            !prefix!pages_sidebars p 
        ON 
            s.id = p.sidebar 
    WHERE p.page = :pageId AND p.enabled = 1) 
     
    ORDER BY sortOrder


    Adding the sortOrder columns and clause, causes it to return the result set x times more, with x being equal to the total count of enabled sidebars.

    How could I make it so that it only returns the single result set?
    Last edited by SpacePhoenix; Dec 23, 2011 at 23:51. Reason: swapped php tags for MySQL tags around SQL query
    Alex Stanford @alexstanford tumblog about.me in fb G+ K
    TechTalkin The Premier Community for Technology Enthusiasts and Professionals
    Full Ambit Media Zero Sacrifice Web Design & Development; Made in the USA @fullambit in fb G+ K

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't think it was the ORDER BY that increased your results tenfold

    rather, it was adding an extra table in the first SELECT and not joining it properly
    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
  •