CODEWITHSUNDEEP
mysqlunionalias
belialek
belialek

Reputation: 79

MySQL - UNION ALL with queries on same table

I would like to use some query that i found on this site - I adapted it for my own purposes:

SELECT * FROM 
(
(
SELECT id, lng FROM stations WHERE lng >= 18.123 ORDER BY lng LIMIT 1
) AS result1
UNION ALL
(
SELECT id, lng FROM stations WHERE lng < 18.123 ORDER BY lng LIMIT 1
) AS result2
)
ORDER BY abs(18.123-lng) LIMIT 1;

But I got an error Syntax error, unexpected AS, expecting UNION_SYM or ')'

When i try to do it without aliases, I (obviously) keep getting error Error Code: 1248. Every derived table must have its own alias

Could you please help me figure out what am I doing wrong? Thank you in advance.

Upvotes: 3

Views: 13739

Answers (1)

VH-NZZ
VH-NZZ

Reputation: 5428

You need to alias the outermost table, as such:

SELECT * FROM 
(
    (SELECT id, lng FROM stations WHERE lng >= 18.123 ORDER BY lng LIMIT 1)
    UNION ALL
    (SELECT id, lng FROM stations WHERE lng < 18.123 ORDER BY lng LIMIT 1)
) AS result12
ORDER BY abs(18.123-lng) LIMIT 1;

EDIT: forgot the parentheses around the inner queries.

Upvotes: 7

Related Questions