UNION / INNER JOIN ORDER BY

[Alex]

(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:

(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?

[r937]

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