CODEWITHSUNDEEP
sql
Mateusz
Mateusz

Reputation: 331

Select name and surname in sql

So I have four tables:

Team:
(PK) ID
Name

Player:
(PK) ID
(FK) Person

PlayerContract:
(PK) ID
(FK) Player
(FK) Team

Person:
(PK) ID
Name
Surname

I have to select all names and surnames of players which are in team X. How can I do it? I wanted to do this in this way:

SELECT 
  name, 
  surname 
FROM 
 Person 
WHERE 
  ID = SELECT Person 
       FROM Player 
       WHERE ID = SELECT Player 
                  FROM PlayerContact 
                  WHERE Team = SELECT ID 
                               FROM Team 
                               WHERE Name = "X";

Is it ok?

Upvotes: 0

Views: 3109

Answers (2)

D Stanley
D Stanley

Reputation: 152521

It would work is you enclosed the subqueries in parentheses:

SELECT 
  name, 
  surname 
FROM 
 Person 
WHERE 
  ID = (SELECT Person 
       FROM Player 
       WHERE ID = (SELECT Player 
                  FROM PlayerContact 
                  WHERE Team = (SELECT ID 
                               FROM Team 
                               WHERE Name = "X")));

Note that the results may be different from a JOIN, for example, if a player is on more than one team (in which case the query above would not work since the second subquery would return two records).

Upvotes: 0

Lamak
Lamak

Reputation: 70638

No, it's not ok, you can easily test your code. Anyway, what you need to do is to JOIN your tables:

SELECT  pe.Name,
        pe.Surname
FROM Player AS pl
INNER JOIN PlayerContract AS pc
    ON pl.ID = pc.Player
INNER JOIN Team AS t
    ON pc.Team = t.ID
INNER JOIN Person AS pe
    ON pc.Person = pe.ID
WHERE t.Name = 'X'

Upvotes: 1

Related Questions