How To Union 2 table selects with WHEN Cases?

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 17, 2015 at 2:56 am

  #1805968

  CREATE TABLE Table1 (ItemId int)

  CREATE TABLE Table2 (ItemId int)

  INSERT INTO Table1 (ItemId) VALUES ('4');

  INSERT INTO Table1 (ItemId) VALUES ('2');

  INSERT INTO Table1 (ItemId) VALUES ('1');

  INSERT INTO Table2 (ItemId) VALUES ('3');

  INSERT INTO Table2 (ItemId) VALUES ('1');

  INSERT INTO Table2 (ItemId) VALUES ('5');

  INSERT INTO Table2 (ItemId) VALUES ('2');

  SELECT ItemId

  FROM (

  SELECT 1 AS whichTable, *, orderCol = CASE WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END

  FROM Table1

  WHERE ItemID IN ('4','2','1')

  UNION ALL

  SELECT 2 as whichTable, *, orderCol = CASE WHEN ItemID = 3 then 4 WHEN ItemID = 1 then 5 WHEN ItemID = 5 then 6 WHEN ItemID = 2 then 7 END

  FROM Table2

  WHERE ItemID IN ('3','1','5','2')

  ) AS data

  ORDER BY whichTable, orderCol;

  Results:

  +--------+

  | ItemId |

  +--------+

  | 4 |

  | 2 |

  | 1 |

  | 3 |

  | 1 |

  | 5 |

  | 2 |

  +--------+

  -- Gianluca Sartori

• scott-903429

  SSC Rookie

  Points: 32

  More actions

  June 17, 2015 at 3:13 am

  #1805970

  Hi Gianluca! Thanks so much for your reply! I just tried the SELECT in management studio and it returned the results but not with the order of the selected values it orders them after ID rather than the selected order?