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