How to write an SQL SELECT statement that returns values from multiple tables?

Question

I've got four tables: person, coach, player, games. I need to write an SQL SELECT statement that will return: my id, name, date of birth, join date, the name of the coach with whom I am registered, and the date, time and duration of the game.

The person table consists of: person id, name, date of birth, and some other unnecessary values. The coach table consists of: coach id, and some other unnecessary values. The player table consists of: player id, join date, joined with. The game table consists of: coach id, player id, game date, game time, game duration.

The person table basically stores all the names and main details both of the coach and of the players. The tables are linked correctly.

I've tried various statements but I totally confused myself. The example is kind of lame, I had something else but not really sure how to go about it anymore.

e.g.

SELECT person_id, full_name, date_of_birth, join_date
FROM (person JOIN player ON player_id = person_id)
WHERE person_id='100' AND person_id, full_name
FROM (person JOIN coach ON coach_id = person_id);

These are the CREATE statements:

CREATE TABLE person (
        person_id          CHAR(10)    NOT NULL,
        full_name          VARCHAR(54)    NOT NULL,
        date_of_birth      DATE,
        sex                CHAR(1),
  PRIMARY KEY (person_id)
) engine innodb;
CREATE TABLE coach (
        coach_id          CHAR(10)    NOT NULL,
        phone_no           CHAR(10)    NOT NULL,
        hall_no            CHAR(4)    NOT NULL,
  PRIMARY KEY (coach_id),
  FOREIGN KEY (coach_id) REFERENCES person (person_id)
) engine innodb;
CREATE TABLE player (
        player_id        CHAR(10)    NOT NULL,
        join_date   DATE        NOT NULL,
        joined_with   CHAR(10),
  PRIMARY KEY (player_id),
  FOREIGN KEY (player_id) REFERENCES person (person_id),
  FOREIGN KEY (joined_with) REFERENCES coach (coach_id)
) engine innodb;
CREATE TABLE game (
        coach_id        CHAR(10)    NOT NULL,
        player_id       CHAR(10)    NOT NULL,
        game_date        DATE        NOT NULL,
        game_time        TIME        NOT NULL,
        game_duration    INTEGER    DEFAULT 10,
  PRIMARY KEY (coach_id, player_id, game_date, game_time),
  FOREIGN KEY (coach_id) REFERENCES coach (coach_id),
  FOREIGN KEY (player_id) REFERENCES player (player_id)
) engine innodb;

Using valid syntax will help. Post the real error messages and/or unexpected output. However, I suggest reading a few "SQL join tutorials" - doing so will allow formulating a better basis question. — user166390
– user166390, Commented Mar 18, 2013 at 22:00
I've added the create statements. There are no error message because I have no statement, I think you've misunderstood the question. Thanks for the -1. I understand that I should have done some more research into this myself and I would have if it wasn't for unforeseen circumstances. I'm sure I can figure this out all by myself, but I need more time which I currently don't have. — Hazzle
– Hazzle, Commented Mar 18, 2013 at 22:13

sgeddes · Accepted Answer · 2013-03-18 22:29:27Z

1

Your SQL is WAY off. It looks like you're trying for the following just using several INNER JOINs:

SELECT p.person_id, p.full_name, p.date_of_birth, pl.join_date, 
  c.coach_id, p2.full_name as coach_name, g.*
FROM person p
    JOIN player pl ON p.person_id = pl.player_id        
    JOIN game g ON p.person_id = g.player_id
    JOIN coach c ON c.coach_id = g.coach_id
    JOIN person p2 on c.coach_id= p2.person_id          
WHERE p.person_id='1' 
ORDER BY p.person_id, p.full_name

You won't need to rejoin on person a second time if you don't need the coach's name -- I just presumed that would be useful information.

SQL Fiddle Demo

edited Mar 18, 2013 at 22:29

answered Mar 18, 2013 at 22:12

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hazzle Over a year ago

That's perfect, just what I needed. This is really helpful to me as I can now reverse engineer it and learn from it. It will be helpful with some of the other statements I have to write. Thanks for linking to SQL Fiddle, it will be useful in the future. I can't vote up just yet, but once I get enough rep points I will. Thank you. :)

sgeddes Over a year ago

@Hazzle -- np, glad I could help. Best of luck!

Jordan · Accepted Answer · 2013-03-18 22:19:34Z

0

I hope this helps.

SELECT person.person_id, person.full_name, person.date_of_birth, player.join_date 
 FROM person
INNER JOIN player
   ON player.player_id = person.person_id
WHERE person.person_id = '100'

edited Mar 18, 2013 at 22:19

answered Mar 18, 2013 at 22:06

Jordan

3381 silver badge10 bronze badges

1 Comment

Hazzle Over a year ago

Thanks for trying to help out, not quite what I needed compared to the above answer, though I would be able to continue from this so thank you once again.

Collectives™ on Stack Overflow

How to write an SQL SELECT statement that returns values from multiple tables?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related