mercredi 17 juin 2015

MSSQL UNION ALL on 2 Table Selects With WHEN Cases

In short, I tried asking for help on an SQL forum...they are very slow to let the post go live and after waiting around 26 hours now decided to try to ask for help at Stack.

I'm running MS SQL 2005. I have 2 tables with the same columns but holding very different data.

SELECT * FROM Table1 WHERE ItemID IN ('4','2','1') ORDER BY CASE  WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END 
UNION ALL 
SELECT * FROM Table2 WHERE ItemID IN ('3','1','5','2') ORDER BY CASE  WHEN ItemID = 3 then 4 WHEN ItemID = 1 then 5 WHEN ItemID = 5 then 6 WHEN ItemID = 2 then 7 END

I need to keep the order of the ItemID in the order that they are selected which is why I used CASE. This all works fine on each table but I can't find a way to combine them into 1 table of results with the results of each table ordered.

ie.

4 (Table1)
2 (Table1)
1 (Table1)
3 (Table2)
1 (Table2)
5 (Table2)
2 (Table2)

Extremely grateful for any and all help.

Aucun commentaire:

Enregistrer un commentaire