minho minho - 6 months ago 15
JSON Question

SQL connecting table and comparing value in same column

Hello I have these two tables:

Team:
team_id
team_name
division


and

Match:
match_id
match_date
...
Team_team_id
Team_team_id1


Example of the Team data:

(1, Anaheim, P)
(2, Arizona, P)
(3, Boston, S)
(4, Buffalo, M)
(5, Detroit, M)


and Match:

(1, date, 1, 2)
(2, date, 2, 3)
(3, date, 5, 3)


and result should be only Anaheim(played against Arizona in same division)

I want to list only team's names, which played matches only against teams in the same division. How could I do that in SQL and relational algebra? Division is only character value. Thank you for any help...

MT0 MT0
Answer
SELECT t1.team_name AS team_name1,
       t2.team_name AS team_name2
FROM   team t1
       INNER JOIN match m
       ON ( t1.team_id = m.team_team_id )
       INNER JOIN team t2
       ON ( t2.team_id = m.team_team_id1 )
WHERE  t1.division = t2.division