vendredi 22 avril 2016

Using OR operator with 2 placeholders with same value

I am a newcomer to Android Programming and looking for some help with my query. I am trying to find all fixtures where the id of a selected team is equal to the home team team_1_id or away team team_2_id

I have the below query, it is not returning any results

public List < Fixture > getTeamFixtures(int id) {
 List < Fixture > fixtureList = new ArrayList < Fixture > ();

 // Select All Query
 String selectQuery = "
SELECT fixtures.id, 
       fixtures.team_1_id, 
       fixtures.team_2_id, 
       fixtures.date, 
       fixtures.time, 
       fixtures.pitch, 
       teams.team_name, 
       teams_1.team_name AS awayTeam, 
       leagues.league_name, 
       leagues.id 
FROM   fixtures 
       INNER JOIN teams 
               ON fixtures.team_1_id = teams.id 
       INNER JOIN teams AS teams_1 
               ON fixtures.team_2_id = teams_1.id 
       INNER JOIN teams_vs_leagues 
               ON teams.id = teams_vs_leagues.team_id 
       INNER JOIN leagues 
               ON teams_vs_leagues.league_id = leagues.id 
WHERE  fixtures.team_1_id = ? 
        OR fixtures.team_2_id = ? 
";

 SQLiteDatabase db = this.getWritableDatabase();
 Cursor cursor = db.rawQuery(selectQuery, new String[] {
  String.valueOf(id)
 });
 // looping through all rows and adding to list
 if (cursor.moveToFirst()) {
  do {
   Fixture fixture = new Fixture();
   fixture.setId(Integer.parseInt(cursor.getString(0)));
   fixture.setTeamId1(Integer.parseInt(cursor.getString(1)));
   fixture.setTeamId2(Integer.parseInt(cursor.getString(2)));
   fixture.setDate(cursor.getString(3));
   fixture.setTime(cursor.getString(4));
   fixture.setPitch(cursor.getString(5));
   fixture.setTeamName1(cursor.getString(6));
   fixture.setTeamName2(cursor.getString(7));
   fixture.setLeagueName(cursor.getString(8));
   fixture.setLeagueId(Integer.parseInt(cursor.getString(9)));
   // Adding team to list
   fixtureList.add(fixture);
  } while (cursor.moveToNext());
 }
 // close inserting data from database
 db.close();
 // return fixturelist
 return fixtureList;

}

Whenever I try

WHERE fixtures.team_1_id = ?

OR

WHERE fixtures.team_2_id = ?

The query returns the relevant results but I want to be able to see whether the id appears in either team_1_id or team_2_id

Any help would be great appreciated.

Aucun commentaire:

Enregistrer un commentaire