CODEWITHSUNDEEP
c#mysqlsql
Cacheira
Cacheira

Reputation: 19

C# MySQL - Difference between 2 queries

im working on a project and for the next part I will need to compare the results of 2 queries.

Scenario: in a table I keep all the players in the team. in another table are only the ones you the coach called for the match.

I want to know which players were left out

What is the best approach for me to take?

Could i use something like

    (Query for Selecting all players)
    EXCEPT
    (Query for Selecting the ones called by the coach)

Tables

ALL PLAYERS

   Number | Name
   ------ | ------
   23     | john 
   24     | Mario

Selected PLAYERS

   Number | Name
   ------ | ------
   23     | john

I want it to give the result that mario is missing from the selected players table

Upvotes: 0

Views: 70

Answers (2)

Utsav
Utsav

Reputation: 8093

Using NOT EXISTS

select * from all_players p1
where not exists
(select 1 from players p2
where p1.number=p2.number
and p1.name=p2.name
--  and -- You can add other columns here
) t

Using LEFT JOIN

select p1.* from all_players p1
left join players p2
on p1.number=p2.number
and p1.name=p2.name
-- and p1.last_name=p2.last_name --add other columns
where 
(p2.number is null 
 and p2.name is null 
 -- and p2.last_name is null --add other columns
)

Using IN, if there is same key to be matched

select * From all_players p
where number not in (select number from players)

Upvotes: 1

Jenish
Jenish

Reputation: 535

User this u can get your result

Select players from players_table where player not in (select coach_selected_player from coach_selected_player_table)

Upvotes: 0

Related Questions