mysql - How can write this query in sql?

Question

Tables:

TEAMS(tid, tname, tcolor, budget)

PLAYERS(pid, pname, age, country)

PLAYS(pid, tid, season, value)

-(pid references to pid in PLAYERS table)

-(tid references to tid in TEAMS table)

WINS(wtid, ltid, season, wscore, lscore)

-(wtid (winner) and ltid (loser) references to tid in TEAMS table)

--

I have to write query for this:(I tried with "not exist" and "not in" but could not do it.)

Find tid of teams that played with all teams.

标签： mysqlsqldatabasesearch

Answer 1

This will do:

SELECT team
FROM (
    SELECT tid team, WINS.ltid otherteam
    FROM TEAMS
    LEFT JOIN WINS ON WINS.wtid = tid
    UNION ALL 
    SELECT tid team, LOSE.wtid otherteam
    FROM TEAMS
    LEFT JOIN WINS LOSE ON LOSE.ltid = tid
)
GROUP BY team
HAVING COUNT(DISTINCT otherteam) >= (SELECT COUNT(tid)-1 AS teamcount FROM TEAMS)otherteams