0

I have a database with 4 tables.

settings

movies

trailers

reviews

final String SQL_CREATE_MOVIE__SETTING_TABLE = "CREATE TABLE " + MovieContract.MovieSettings.TABLE_NAME + " (" +
            MovieContract.MovieEntry._ID + " INTEGER PRIMARY KEY, " +
            MovieContract.MovieSettings.COL_MOVIE_SETTING + " TEXT UNIQUE NOT NULL " +
            " );";

    final String SQL_CREATE_MOVIE_TABLE = "CREATE TABLE " + MovieContract.MovieEntry.TABLE_NAME + " (" +
            MovieContract.MovieEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            MovieContract.MovieEntry.COL_MOVIE_KEY + " INTEGER NOT NULL, " +
            MovieContract.MovieEntry.COL_MOVIE_ID + " INTEGER NOT NULL, " +
            MovieContract.MovieEntry.COL_BACKDROP_PATH + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COL_ORIGINAL_LANGUAGE + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COL_ORIGINAL_TITLE + " TEXT NOT NULL," +
            MovieContract.MovieEntry.COL_OVERVIEW + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COL_TITLE + " TEXT NOT NULL, " +
            MovieContract.MovieEntry.COL_VOTE_AVERAGE + " REAL NOT NULL, " +
            MovieContract.MovieEntry.COL_VOTE_COUNT + " INTEGER NOT NULL, " +
            MovieContract.MovieEntry.COL_POSTER_PATH + " TEXT NOT NULL, "  +
            MovieContract.MovieEntry.COL_DATE + " TEXT NOT NULL, " +

            // Set up the location column as a foreign key to location table.
            " FOREIGN KEY (" + MovieContract.MovieEntry.COL_MOVIE_KEY + ") REFERENCES " +
            MovieContract.MovieSettings.TABLE_NAME + " (" + MovieContract.MovieSettings._ID + "), " +

            // To assure the application have just one weather entry per day
            // per location, it's created a UNIQUE constraint with REPLACE strategy
            " UNIQUE (" + MovieContract.MovieEntry.COL_DATE + ", " +
            MovieContract.MovieEntry.COL_MOVIE_KEY + ") ON CONFLICT REPLACE);";

    final String SQL_CREATE_TRAILER_TABLE = "CREATE TABLE " + MovieContract.TrailerEntry.TABLE_NAME + " (" +
            MovieContract.TrailerEntry._ID + " INTEGER PRIMARY KEY," +
            MovieContract.TrailerEntry.COL_MOVIE_ID + " INTEGER NOT NULL, " +
            MovieContract.TrailerEntry.COL_TRAILER_ID + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_ISO_369_1 + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_KEY + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_NAME + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_SITE + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_SIZE + " TEXT NOT NULL, " +
            MovieContract.TrailerEntry.COL_TYPE + " TEXT NOT NULL, " +


            // Set up the movie_id column as a foreign key to movie table.
            " FOREIGN KEY (" + MovieContract.TrailerEntry.COL_MOVIE_ID + ") REFERENCES " +
            MovieContract.MovieEntry.TABLE_NAME + " (" + MovieContract.MovieEntry.COL_MOVIE_ID + ") " +

            " UNIQUE (" + MovieContract.TrailerEntry.COL_MOVIE_ID + ", " +
            MovieContract.TrailerEntry.COL_TRAILER_ID + ") ON CONFLICT REPLACE);";

    final String SQL_CREATE_REVIEW_TABLE = "CREATE TABLE " + MovieContract.ReviewEntry.TABLE_NAME + " (" +
            MovieContract.ReviewEntry._ID + " INTEGER PRIMARY KEY," +
            MovieContract.ReviewEntry.COL_MOVIE_ID + " INTEGER NOT NULL, " +
            MovieContract.ReviewEntry.COL_REVIEW_ID + " TEXT NOT NULL, " +
            MovieContract.ReviewEntry.COL_AUTHOR + " TEXT NOT NULL, " +
            MovieContract.ReviewEntry.COL_CONTENT + " TEXT NOT NULL, " +
            MovieContract.ReviewEntry.COL_URL + " TEXT NOT NULL, " +

            // Set up the movie_id column as a foreign key to movie table.
            " FOREIGN KEY (" + MovieContract.ReviewEntry.COL_MOVIE_ID + ") REFERENCES " +
            MovieContract.MovieEntry.TABLE_NAME + " (" + MovieContract.MovieEntry.COL_MOVIE_ID + ") " +

            " UNIQUE (" + MovieContract.ReviewEntry.COL_MOVIE_ID + ", " +
            MovieContract.ReviewEntry.COL_REVIEW_ID + ") ON CONFLICT REPLACE);";

I want for now to inner join the the trailers tables into the movie table. So the new trailers table should have the following columns.

----------------------------------------------------------------------------------
|_id  |movie_id |poster_path  |id  |iso_639_1  |key  |name  |site  |size  |type  |

Both movie_id and poster_path columns belong to the movies table. I perform the inner join as

sTrailerByMovieIdQueryBuilder.setTables(
            MovieContract.TrailerEntry.TABLE_NAME + " INNER JOIN " +
            MovieContract.MovieEntry.TABLE_NAME + " ON " +
            MovieContract.TrailerEntry.TABLE_NAME + "." + 
            MovieContract.TrailerEntry.COL_MOVIE_ID + " = " +
            MovieContract.MovieEntry.TABLE_NAME + "." + 
            MovieContract.MovieEntry.COL_MOVIE_ID + " AND " +
            MovieContract.MovieEntry.TABLE_NAME + "." + 
            MovieContract.MovieEntry.COL_POSTER_PATH
        );

But I get this table 

--------------------------------------------------------------------
|_id  |movie_id |id  |iso_639_1  |key  |name  |site  |size  |type  |

The poster_path column is missing. I did something wrong in my inner join but I don't know how to fix it.

Thanks,

Theo.

Theo
  • 3,099
  • 12
  • 53
  • 94
  • After AND you mentioned only one table column. https://stackoverflow.com/questions/2366780/how-to-do-an-inner-join-on-multiple-columns – Mohit Nov 22 '17 at 08:39

1 Answers1

0

The reason why poster_path column is missing is because you failed to call another inner join in your query.

Currently, your query looks like this:

trailers 
INNER JOIN movies
ON trailers.movie_id = movies.movie_id
AND movies.poster_path

Your query should look like this:

... 
INNER JOIN movies m1
ON trailers.movie_id = m1.movie_id
INNER JOIN movies m2
ON trailers.poster_path = m2.poster_path
ppgdn
  • 23
  • 7