Aniket Shinde Aniket Shinde - 9 days ago 6
Android Question

Android: One cursor row for 2 joined tables

I have 2 tables, let's call them match and team. Match has a one to two relation with Team.

match has

_id = INTEGER PRIMARY
time = TEXT NOT NULL
team1_id = TEXT NOT NULL //references a team record
team2_id = TEXT NOT NULL //references a team record


team has

_id = INTEGER PRIMARY
name = TEXT NOT NULL


Each item of my list contains time, team1_name and team2_name. Populated by cursor. It looks something like this:

List Item Image

Now when I use join query:

Select match._id, time, team.name
From match LEFT JOIN team
ON (team._id = match.team1_id OR team._id = match.team2_id)


I receive the following cursor result:

_id time name
42 6:00 Barca
42 6:00 Man City


What I am trying to achieve is have the two team names in two columns of the cursor instead of separate rows inorder to map it with the adapter's view i.e:

_id time team1 team2
42 6:00 Barca Man City


Any tips on how to go about it?
Thanks.

Answer

You'll need to do multiple joins and alias the column names:

SELECT match._id, 
       time, 
       t1.name AS team1_name,
       t2.name AS team2_name 
  FROM match 
  LEFT JOIN team AS t1 ON (t1._id = match.team1_id)
  LEFT JOIN team AS t2 ON (t2._id = match.team2_id)
Comments