CODEWITHSUNDEEP
sqlsql-servert-sql
Arzybek
Arzybek

Reputation: 782

Select rows that don't appear in other table

Here is the problem: I have table of matches of soccer, every row has one country and id of match, for example: 

ID1 Russia;
ID1 Egypt

Means it was game between Russia and Egypt. But in table I have rows only for teams that scored a goal. For example if it was USA - Canada 2-0 in table I will have:

ID2 USA GOALS: 2

And I have a table of all games, where it will be row with ID2 Canada but without goals. What I'm trying to do is add to new table rows with zero goals to countries who didn't score, like:

ID2 USA GOALS:2;
ID2 CANADA GOALS:0

But I'm getting something weird. Here's some screenshots to see:

Columns: Date, Group, Country, Id, Goals. This is table of countries who scored

Table of all games: Id, Group, Country, Date

Now I want to check if there is row in first table for ID & Country and if its not, that means country didn't scored and I should add new row with this ID, Country and zero goals. I tried something like this, but it didn't work: 

select #AllCountries.Date, #AllCountries.Group, #Country, #Id, 0 as Goals
    from #AllCountries inner join #CountriesWhoScored
    ON #AllCountries.Id = #WhoScored.Id
    WHERE #AllCountries.Id = #WhoScored.Id AND #AllCountries.Country != #WhoScored.Country

But I'm getting duplicates of rows which already exists in table with countries who scored:

For example at least one country of 4ID shouldnt be here

P.S: It's SQL Server 17 and I'm working in SSMS

Upvotes: 2

Views: 103

Answers (1)

GMB
GMB

Reputation: 222402

I thnk that you just need a left join and coalesce():

select c.*, coalesce(s.Goals, 0)
from #AllCountries c
left join #CountriesWhoScored s on s.Id = c.Id and s.Country = c.Country

For a country that did not score, s.Goals ends up null: then you can use coalesce() to turn it to 0.

Upvotes: 4

Related Questions