CODEWITHSUNDEEP
sql-servert-sql
UndeadEmo
UndeadEmo

Reputation: 523

Combining two tables in SQL Server

I have two tables in my database

Winners table:

Count   Country
--------------------
5       Hungary
2       Ireland
1       Italy
3       Netherlands

RunnerUp table:

Count     Country
----------------------
1         Italy
3         Netherlands
2         Spain

How do I combine these two tables to look like this:

Country     Winners    RunnerUp
--------------------------------
Hungary       5          0
Ireland       2          0
Italy         1          1
Netherlands   3          3
Spain         0          2

Upvotes: 1

Views: 63

Answers (4)

Bruce David Wilner
Bruce David Wilner

Reputation: 467

Simpler and — much more importantly — standards-compliant rather than relying upon nonsense functions like COALESCE() that are not needed in an already functionally complete RDBMS:

CREATE TABLE RESULTS( COUNTRY, WINNERS, RUNNER_UP ) PRIMARY KEY( COUNTRY );

INSERT INTO RESULTS  
SELECT W.COUNTRY, W.COUNT AS WINNERS  
FROM WINNERS W  
INNER JOIN ON W.COUNTRY = R.COUNTRY  
SELECT R.COUNTRY, R.COUNT AS RUNNER_UP  
FROM RUNNERS_UP R;

Upvotes: 0

starx207
starx207

Reputation: 367

I would use case statements in your select with a correlated sub query using EXISTS to determine what value to show for a country (0 if it doesn't exist, the value in the table if it does).

Something along these lines:

SELECT 
    countries.Country,
    CASE 
       WHEN EXISTS(SELECT Winners.Country 
                   FROM Winners 
                   WHERE countries.Country = Winners.Country) 
          THEN (SELECT SUM(Count) 
                FROM Winners 
                WHERE countries.Country = Winners.Country) 
       ELSE 0 
    END AS Winners,
    CASE 
       WHEN EXISTS(SELECT RunnerUp.Country 
                   FROM RunnerUp 
                   WHERE countries.Country = RunnerUp.Country) 
          THEN (SELECT SUM(Count) 
                FROM RunnerUp 
                WHERE countries.Country = RunnerUp.Country) 
       ELSE 0 
    END AS RunnerUp
FROM 
    (SELECT Country FROM Winners
     UNION
     SELECT Country FROM RunnerUp) as countries

I union the country names so the results have all countries whether or not they appear in both tables. The SELECT statement in the THEN part of the case statements can only return a single value, so I used the sum() aggregate. This way, if for some reason a country shows up in the same table more than once, I'll get the total for both times.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311326

The construct you're looking for is a full outer join:

SELECT          COALESCE([winners].[country], [runnerup].[country]) AS country,
                COALESCE([winners].[count], 0) AS winners, 
                COALESCE([runnerup].[count], 0) AS runnerup
FROM            [winners]
FULL OUTER JOIN [runnerup] ON [winners].[country] = [runnerup].[country]

Upvotes: 0

objectNotFound
objectNotFound

Reputation: 1783

Using Union

Select Country , Sum(Winners) as Winners, Sum(RunnerUp)  as RunnerUp 
FROM
(
Select Count as Winners , 0 as RunnerUp, Country from Winners 
Union ALL
Select 0  as Winners , Count as RunnerUp, Country from RunnerUp
) a
Group By Country

Upvotes: 1

Related Questions