CODEWITHSUNDEEP
sqljoinleft-join
UniqueName
UniqueName

Reputation: 103

Return rows where no match is found in another table

Couldn't find a question that had this exact problem.

I have 2 tables, I want to return the rows from one table where the row doesn't exist in the second table.

I will make an example to show.

The first table:

teamName
United
Athletic
Football
Rugby

The second table:

Username team
Gary          United
Gary          United
Phil          Rugby
Phil          Rugby

So what I want to do is select all the teams from the first table that don't appear in the second table. However the problem is I want only want it to compare against the rows where the Username is Gary.

So selecting all the rows from the first table where the team name does not exist in the second table and the username is Gary for example.

I managed to pull all the rows where the team name does not exist in the second table via a left join, however I am not sure how to add the condition where the username equals something.

I realise I may have to change the structure of my tables. I am open to suggestions?

Upvotes: 0

Views: 64

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Use NOT EXISTS clause to find the teams that is not present in table2

SELECT t.*
FROM   teams t
WHERE  NOT EXISTS (SELECT 1
                   FROM   userteams ut
                   WHERE  t.teamname = ut.teamname
                          AND ut.name = 'Gary')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

if you want to find all teams that don't have a player named Gary, using a left outer join:

select t.*
from teams t left join
     userteams ut
     on t.teamname = ut.teamname and ut.name = 'Gary'
where ut.username is null;

Upvotes: 1

Related Questions