CODEWITHSUNDEEP
sqldatabasepostgresqlcsv
Pamroni
Pamroni

Reputation: 1

PostgreSQL match a string from a nested query

I am trying to run a query where we find movies based on an actors name. When I run

SELECT known_titles
from name_basics
where name like 'Tom Cruise'

The datasource we have returns a single TEXT field of tt0181689,tt0092099,tt0116695,tt0325710.

How could I use this to query again? My main query is: 

SELECT movie_name
from MOVIE_INFO
where movie_id like (SELECT known_titles
                     from name_basics
                     where name like 'Tom Cruise'
                    )

But this is returning nothing. Is there a way I can do a movie_id wildcard search in PostgreSQL that will see what is similar? Something like SELECT movie_name from MOVIE_INFO where movie_id in tt0181689,tt0092099,tt0116695,tt0325710?

The list of tt0181689,tt0092099,tt0116695,tt0325710 is stored as TEXT in a single cell and is copied from a CSV file using PostgreSQL \copy command.

Upvotes: 0

Views: 234

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

The query with the subselect will cause an error if the subselect returns more than one row. Your code probably doesnt handle errors appropriately, but ignores them, so that the result appears empty.

You will have to learn the basic SQL concept of “join”:

SELECT movie_info.movie_name
FROM movie_info
   JOIN name_basics
      ON movie_name.movie_id = name_badics.known_titles
WHERE name_basics.name = 'Tom Cruise'

Remarks:

  • The table and column names are not very suggestive.

  • Using LIKE doesn't make sense if there is no wildcard in the pattern.

  • The last sentence of your question does not make sense to me at all. The list is the result of a query: how can it come from a file at the same time?

Upvotes: 1

Related Questions