I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments

Question

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This is my code

Select distinct (first_name) 
from player, player_registration, tournament
where tournament.tournament_id = player_registration.tournament_id
and player.player_id = player_registration.player_id
and player.player_id in 
(Select player.player_id from tournament 
 where tourn_cat = "Masters 1000" and
 tourn_cat ="Grand Slam" );

It works when it's just masters 1000 or grand slam but i can't figure out how to get them together. With this query, nothing shows up I attached a photo of part of the database

Answer 1

This will return the players which have been registered in both tournaments:

    SELECT *
    FROM   player AS p
    WHERE  EXISTS (
                     SELECT 1
                     FROM   player_registration AS pr
                            JOIN tournament AS t ON t.tournament_id = pr.tournament_id
                     WHERE  t.tourn_cat = 'Masters 1000'
AND pr.player_id = p.player_id
                  )
           AND EXISTS (
                         SELECT 1
                         FROM   player_registration AS pr
                                JOIN tournament AS t ON t.tournament_id = pr.tournament_id
                         WHERE  t.tourn_cat = 'Grand Slam' AND pr.player_id = p.player_id
                      );

Answer 2

Not sure I understand your question, but the and condition in your last sub-select is likely wrong. No row will satisfy that:

Select distinct first_name
from player, player_registration, tournament
where tournament.tournament_id = player_registration.tournament_id
  and player.player_id = player_registration.player_id
  and player.player_id in 
      (Select player.player_id 
       from tournament 
       where tourn_cat = "Masters 1000" 
         OR tourn_cat ="Grand Slam" );

Your query will be easier to read with ANSI joins and aliases. I also used standard string delimiters, " is for quoting

Select distinct first_name
from player p
JOIN player_registration pr
    ON p.player_id = pr.player_id
JOIN tournament t
    ON t.tournament_id = pr.tournament_id
 WHERE p.player_id in 
      (Select p.player_id 
       from tournament 
       where tourn_cat = 'Masters 1000'
          OR tourn_cat ='Grand Slam');

Now, this does not look right. You can probably simplify to

Select first_name
from player p
JOIN player_registration pr
    ON p.player_id = pr.player_id
JOIN tournament t
    ON t.tournament_id = pr.tournament_id
 WHERE t.tourn_cat IN ('Masters 1000', 'Grand Slam');
 GROUP BY first_name
 HAVING COUNT(distinct t.torn_cat) = 2