CODEWITHSUNDEEP
sql
user1390252
user1390252

Reputation: 35

SQL rows from different tables

I have two tables that I want information from. The name of the first table is Playing, the other is Game.

Player Game_ID Points

Game_ID Length Judged_by

I want to write an SQL query that only shows the players who have the same game_ID. And also the length of that game. I'm pretty new to SQL and don't know how to do this.

Upvotes: 0

Views: 136

Answers (1)

juergen d
juergen d

Reputation: 204784

Not quite sure what you want to achive. If you want all users that have more that 1 game played use:

select p.player, g.game_id, g.length 
from game g
inner join playing p on g.game_id = p.game_id
group by game_id
having count(game_id) > 1

If you want all players that played a specific game then use (game_id is an example):

select p.player, g.game_id, g.length 
from game g
inner join playing p on g.game_id = p.game_id
where game_id = 123

Edit

Here you go:

select p.player, g.game_id, g.length
from game g 
inner join playing p on g.game_id = p.game_id
where g.game_id in (  select g.game_id
                      from game g
                      inner join playing p on g.game_id = p.game_id
                      group by g.game_id
                      having count(p.game_id) > 1
                    )
order by g.game_id

Please see this SQLFiddle example to verify that it is working.

Edit 2

I don't know the relation between playing and players table. I used columns id and player in this example to get the players name too:

select g.game_id, g.length, p.first_name, p.last_name
from game g 
inner join playing pg on g.game_id = pg.game_id
inner join Players p on p.id = pg.player
where g.game_id in (  select g.game_id
                      from game g
                      inner join playing pg on g.game_id = pg.game_id
                      group by g.game_id
                      having count(pg.game_id) > 1
                    )
order by g.game_id

Last edit:

select g.game_id, g.length, p.first_name, p.last_name, j.Judge_firstname, j.Judge_lastname
from game g 
inner join playing pg on g.game_id = pg.game_id
inner join Players p on p.id = pg.player
inner join judges j on j.judge_id = g.judged_by
where g.game_id in (  select g.game_id
                      from game g
                      inner join playing pg on g.game_id = pg.game_id
                      where play_date > to_date('2012-05-01', 'YYYY-MM-DD')
                      group by g.game_id
                      having count(pg.game_id) > 1
                    )
order by g.game_id

See this example

Upvotes: 4

Related Questions