Search code examples
sqlsql-serverjoinunioncoalesce

combine/merge two columns into 1 ( t-sql )

I have 2 tables: batters and pitchers

I want to pull 

playerid(nvarchar50), firstname(nvarchar50), lastname(nvarchar50), bats(nvarchar50)

from the batters table

and 

playerid(nvarchar50), firstname(nvarchar50), lastname(nvarchar50), throws(nvarchar50)

from the pitchers table

I want to combine the output so that when i get results it comes out like this

playerid, firstname, lastname, throws, bats

Is this possible? I'm guessing it should be but I've exhausted joins and unions and cant get the result set to come out that way. Remember they are two different tables

bat table 

playerID    nameFirst   nameLast    bats
----------------------------------------
abreubo01   Bobby       Abreu       L
abreujo02   Jose        Abreu       R
abreuto01   Tony        Abreu       B
ackledu01   Dustin      Ackley      L
adamecr01   Cristhian   Adames      S
adamsla01   Lane        Adams       R
adamsma01   Matt        Adams       L

pit table

playerid    nameFirst   nameLast    throws
------------------------------------------
abadfe01    Fernando    Abad        L
aceveal01   Alfredo     Aceves      R
achteaj01   A.J.        Achter      R
adamsau01   Austin      Adams       R
adamsmi03   Mike        Adams       R
adcocna01   Nathan      Adcock      R
affelje01   Jeremy      Affeldt     L

Desired Result

pit table

playerid    nameFirst   nameLast    throws  Bats
------------------------------------------------
abadfe01    Fernando    Abad              
aceveal01   Alfredo     Aceves              
achteaj01   A.J.        Achter             
adamsau01   Austin      Adams       
adamsmi03   Mike        Adams      
adcocna01   Nathan      Adcock     
affelje01   Jeremy      Affeldt
Solution

  • Assumptions:

    1) You just want a list of all the players without any restrictions

    2) The same player ID never appears in both tables (or if it does, you don't care if they're listed twice)

    Based on those assumptions, you can simply write:

    SELECT playerID, nameFirst, nameLast, bats, NULL as throws
FROM bats
UNION ALL
SELECT playerID, nameFirst, nameLast, NULL as bats, throws
FROM pits

    However, I think your data is not fully normalised. Both of these tables are actually lists of players, just with a slight variation in their attributes. So a more sensible overall approach would be simply to have a single "players" table with columns as follows:

    playerID, nameFirst, nameLast, bats, throws

    Bats and throws would both be nullable, in case the player doesn't do that action. If necessary you could add an extra "player_type" column to denote their role (batter, pitcher - or both, if that's allowed).

    Once you've got that structure, the query is trivial:

    SELECT playerID, nameFirst, nameLast, bats, throws
FROM players