UNION select... problems

[eluser]hugle[/eluser]
Hello my dear friends
I came up with a problem here... I never used union selects, but Now I have probems with them

Code:

(SELECT id, name AS name1, created FROM tehitrosti ORDER BY created DESC LIMIT 10)
UNION
(SELECT id, name AS name2, created FROM statji ORDER BY created DESC LIMIT 10)
ORDER BY created DESC LIMIT 10

Query itself is working nice, it's selecting data from both tables, BUT ...
the problem is in AS function.
The problem is that the alias of `name1` is given to ALL the results, but not the results from first query. and `name2` is not given to any of the results.

I was looking the examples at MySQL forums, and seems that my query should be working nicely, but somehow it does not, and I do not understand what I am mising here...

Thanks for any of your inputs

cheers,
huglester

[eluser]Unknown[/eluser]
Unfortunately, that's how the union queries work.

What you could do instead is this:

Code:

(SELECT id, name, 1 as sort, created FROM tehitrosti ORDER BY created DESC LIMIT 10)
UNION
(SELECT id, name, 2 as sort, created FROM statji ORDER BY created DESC LIMIT 10)
ORDER BY created DESC LIMIT 10

This way you can check the sort column's value to see if it's from the first or the second table.

Hope this helps.