CODEWITHSUNDEEP
sqlmysql
baklap
baklap

Reputation: 2173

How to search for multiple values on inner join

I've got two tables, one with games and one with participants.

A game can have multiple participants. Now I need to search if a game is already inserted. I made a query with an inner join.

SELECT game.gameId
FROM game 
INNER JOIN participants 
WHERE game.gameId = participants.gameId 
AND participants.name = 'Team1' 
AND participants.name = 'Team2'

This isn't working the way I expected, is there a way to check if there is a match between teams 1 and 2 in one query?

Thanks!

edit

tablelayout:

**game**
PK gameId
date

**participants**
PK id
FK gameId
name
type //home or visiting

Upvotes: 3

Views: 8452

Answers (3)

knittl
knittl

Reputation: 265291

you need to join twice with the participants table:

SELECT game.gameId
FROM game
INNER JOIN participants p1
ON game.gameId = p1.gameID
INNER JOIN participants p2
ON game.gameId = p2.gameID
WHERE (p1.name = 'Team1' AND p2.name = 'Team2')
OR (p2.name = 'Team1' AND p1.name = 'Team2')

Upvotes: 1

Lou Franco
Lou Franco

Reputation: 89192

You're not trying to relate game to participants, but participants to itself.

select 
   p1.gameId 
from 
   participants as p1, participants as p2 
where
   p1.name = 'Team1' and p2.name='Team2' and p1.gameId = p2.gameId

Upvotes: 4

Jason McCreary
Jason McCreary

Reputation: 73001

The following should work. It will JOIN games and participants using the gameID and ensure that the names are Team1 and Team2. This assumes that participants is a many to many with games and that there are only two teams per game.

SELECT participants.gameId
FROM game 
JOIN participants 
ON game.gameId = participants.gameId 
WHERE participants.name = 'Team1' 
OR participants.name = 'Team2'
GROUP BY participants.gameID
HAVING COUNT(*) = 2

Upvotes: 8

Related Questions