1

Scenario: I have 2 tables - TableA and TableB. Each table has a column that stores a list of movie titles. These two columns are not labeled as the same column_name so you can't join them as column_name (for example, they're labeled as - TableA.movie_titles, TableB.titles_movie - both hold movie titles). However, the tables share 1 column that you can join on which stores the id of the movie titles and is labeled movie_id (So there is a TableA.movie_id that you can join onto TableB.movie_id).

My objective is to see/return the list of the movie titles that appear in both of these columns only (Example, "Finding Nemo" is listed in both TableA.movie_title and TableB.title_movie). I assume that I need to join the two tables so that I can create this list. I'm also limited in that I am only able to join each table on the 1 column that they both share, which is labeled movie_id.

I have:

 SELECT TableA.movie_titles, TableB.titles_movie
 FROM TableA 
 JOIN TableB ON TableA.movie_id = TableB.movie_id 
 WHERE EXISTS(SELECT TableA.movie_titles, TableB.titles_movie 
         FROM TableB 
         WHERE TableA.movie_id = TableB.movie_id
         AND TableB.titles_movie <> TableA.movie_title)

When I run this query, TableA will show me duplicate movie titles, while Table B has no duplicates. This is not what I'm looking for.

I based my query off of this question, but I am not sure if I am using the right query for my scenario.

Should I use some form of a True/False statement that will help me match the movie titles? What should I be doing here?

funkemaeby
  • 11
  • 3

2 Answers2

1

You can use a simple query like this:

SELECT TableA.movie_titles, TableB.titles_movie
FROM TableA 
JOIN TableB ON TableB.titles_movie = TableA.movie_title

This will give all movies that appear in both tables. This uses exact match, so the title of the movie must be identical in both tables including spaces, characters, and casing.

If you want to ignore casing, you can use the LOWER() function:

SELECT TableA.movie_titles, TableB.titles_movie
FROM TableA 
JOIN TableB ON LOWER(TableB.titles_movie) = LOWER(TableA.movie_title)

But this query will not use any existing index on those two columns unless the indexes are built using LOWER().

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
0

i think you could give result by using the following code:

select TableA.Movie_titles from tableA
where TableA.Movie_titles in (select distinct TableB.titles_movie from tableB)
MSN
  • 173
  • 4
  • 12