how to use the sqlite query to retrieve the data that i need from three table?

Question

i have android application that use the sqlite database pre populated the problem is that i need to make a select query from 3 tables where the first table

venue

• _id
• name
• city

team

• _id
• group_id
• code
• name

match

• _id
• stage
• venue_id
• team1_id
• team2_id
• kickoff

what i need is to make a select query that have the name from venue, kickoff from match, name from team and name from team

where the first name is the first team and the second name is the second team

this is my query :

select v.name,m.[kickoff],t.name,stage
from venue v,match m,team t
where m.venue_id = v._id
and 
t._id = m.team1_id
and 
t._id = m.team2_id

Answer 1

I think this query should be what you are after:

SELECT v.Name, m.[kickoff], team1.name, team2.name, stage
    FROM Match AS m
    INNER JOIN Venue AS V ON m.venue_id = v._id
    INNER JOIN Team AS team1 ON m.team1_id = team1._id
    INNER JOIN Team AS team2 ON m.team2_id = team2._id

I think the problem that are having with your original query is that there are actually 2 teams linked to a match but you are only accounting for one, and the last part of your query:

t._id = m.team1_id
and 
t._id = m.team2_id

... will only be true if the team is playing itself.