Search code examples
androidsqlitejoincursor

SQL: Table with match-results needs team information added, based on team-ids. How do I do this for home and away-teams in one query?

I am programming on Android and only starting with SQL:

What I have are 2 SQL tables, table A contains a list of match-results of sports games, table B contains all information about the teams.

Table A has two team-ids, one for the home-team, one for the away-team.

I want to create a sql query, that gets a match-result for every match, that is linked up to the corresponding teams, e.g. the two team-ids in table A should get replaced by at least the team-name (preferably more columns) of table B.

So in short: For every match in table A -> get all match-info from table A -> add information for the home and the away team from table B, corresponding to home-id and away-id from table A -> deliver result

I achieved this party through an SQL JOIN, but I only managed to join the information for either the home- or the away-team, not both, since the columns get in conflict, since both the home and away team information come from the same table and thus the column names are the same (home team has "team_name", and away team also has "team_name" -> conflict)

How can I achieve this?

For some information on why I would like to do this in one query: I am working on Android, displaying a list of match-results, which are loaded asynchronously via a Loader, which feeds a Cursor to a CursorAdapter. As I understand the cursor, it's its nature to only deliver one result per row of the list, thus all the data querying has to be done in one sql query.

Thank you for your help!

EDIT: My current sql join is this

ScheduleTable.TABLE_SCHEDULE + " JOIN " + TeamsTable.TABLE_TEAMS + " ON " +
                    ScheduleTable.TABLE_SCHEDULE+"."+ScheduleTable.COLUMN_HOME_TEAM_ID + " = " + TeamsTable.TABLE_TEAMS+"."+TeamsTable.COLUMN_ID
Solution

  • The easiest way to look up a value from another table might be a subquery:

    SELECT Date,
       Result,
       (SELECT Name FROM Teams WHERE ID = Schedule.HomeTeamID
       ) AS HomeTeamName,
       (SELECT Name FROM Teams WHERE ID = Schedule.AwayTeamID
       ) AS AwayTeamName
FROM Schedule

    However, this becomes unwieldy if you need to look up more than one column for a team.

    To be able to address a single table that you are using multiple times in the same FROM clause, you must use table aliases:

    SELECT Schedule.Date,
       Schedule.Result,
       HomeTeam.Name,
       AwayTeam.Name
FROM Schedule
JOIN Teams AS HomeTeam ON Schedule.HomeTeamID = HomeTeam.ID
JOIN Teams AS AwayTeam ON Schedule.AwayTeamID = AwayTeam.ID