[Solved] Sport SQL query improvement [closed]

Question

I’ll cut you a break since MySQL can be difficult and confusing starting out. You should research how these queries work. If you’re studying SQL: the way I learn most effectively, personally, is through lots of exercises which make me think about different sorts of SQL queries – so do a lot of exercises.

Player names for both teams

How can show all the players from both ~~games~~ teams by their names?

I’m going to assume you mean teams, not games. Given a game, do this:

SELECT Player.team_id AS team_id,
    Player.player_name AS player_name
FROM Player
INNER JOIN Game
    ON (
        (Game.Home_team_id = Player.team_id)
        OR (Game.Guest_team_id = Player.team_id))
WHERE Game.game_id = [YOUR GAME ID];

This will produce results like:

team_id     player_name
1           John Smith
1           Jane Doe
2           John Person

Team with the most wins

How can i find out the team who has more win than the rest of the teams?

Since there doesn’t appear to be anything saying who won, I can’t really tell you how to write such a query. There’s only a single score field and I’m not sure what it contains. I can’t really circle around that either, since the query would look radically different depending on what data’s available to work from, and I don’t like to make assumptions (it might be a waste of time for both of us).

If it contains, say, JSON or XML data, or some other data format containing a set of scores, you need to break that apart into its own cells. Storing a collection of data inside a table cell, like a JSON collection of scores for instance, is fine – as long as you never, ever need to query it. Once you need to query it, that turns into a mess, and you need to avoid that sort of situation.

Accepted Answer