How can write this query in sql?
Multi tool use
Clash Royale CLAN TAG#URR8PPPHow can write this query in sql?
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.
2 Answers
2
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
My MySql Workbench crashed 2 times after trying to execute that query. So it didn't work. I don't know why.
– yasin
Jul 23 at 14:01
@yasin if you want more help you'll have to edit your question as I mentioned in my comment above
– ADyson
Jul 24 at 9:29
This worked for me.
SELECT
t1.tid
FROM
teams t1
WHERE
NOT EXISTS( SELECT
t2.tid
FROM
teams t2
WHERE
t2.tid NOT IN (SELECT
wtid
FROM
wins
WHERE
ltid = t1.tid UNION (SELECT
ltid
FROM
wins
WHERE
wtid = t1.tid)) AND t1.tid != t2.tid)
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
1) please show what you tried, even if it didn't work. Then we know you did try. 2) Please explain the structure of your database. 3) You've shown us some fields but not explained what the output of the query should be (i.e what the rules are, what should / should not be included, and what the criteria for that are). Ideally show us some example source data and example of what they query output would be based on that source data. Right now, given only what you've written above, it's not possible to answer your question properly.
– ADyson
Jul 23 at 10:39