SQL Server 2014 UNION in CROSS APPLY

Question

I have the following query

SELECT DISTINCT 
    d.UserName, 
    i.itemID, 
    d.Score, 
    d.StoreCode, 
    d.Location
FROM 
    G.dbo.Users d
LEFT JOIN 
    G.dbo.Emails s on d.UserName=s.UserName
CROSS APPLY 
    (
        SELECT TOP (1) 
            ii.ItemID 
        FROM 
            G.dbo.Dump ii
        WHERE 
            ii.Username=d.UserName
        AND 
            ii.endTime>DATEADD(hh,3,getDate())
    ) i
WHERE 
    s.serName is null
AND 
    d.Score>@_Score
AND 
    (d.processed=0)
GROUP BY 
    d.UserName, 
    i.itemID, 
    d.Score, 
    d.StoreCode, 
    d.Location
ORDER BY 
    d.UserName ASC

Now I need to modify it since Table G.dbo.Dump has been splitted into 20 smaller tables and now I have Dump_00 to Dump_19
I try to modify part of the CROSS APPLY section using UNION in this way

CROSS APPLY 
(
    SELECT TOP (1) 
        ii.ItemID
    FROM
        (
            SELECT TOP (1) FROM G.dbo.Dump_00
            UNION
            SELECT TOP (1) FROM G.dbo.Dump_01
            UNION
            .....
            SELECT TOP (1) FROM G.dbo.Dump_19
        ) ii
    WHERE 
        ii.UserName=d.UserName
    AND 
        ii.EndTime>DATEADD(hh,3,getDate())
) i

but result is not working as expected

can suggest if UNION is the right way and in case how to apply, or another solution?

Thanks!

Answer 1

Remove the TOP 1 from the union elements. Not sure why that was added. Logically, you are after a set that is the union of all tables.

Also, I don't think you want a union at all. You want the concatenation.

CROSS APPLY 
(
    SELECT TOP (1) ii.ItemID
    FROM
        (
            SELECT FROM G.dbo.Dump_00 --changed
            UNION ALL --changed
            SELECT FROM G.dbo.Dump_01 --changed
            .....
        ) ii
) i