PostgreSQL match a string from a nested query

Question

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.

It is difficult to say why your query returns empty without seeing the structure of your tables and your data. I suggest you manually query the data using the output of the first query to make sure that your column data does not have leading or trailing spaces. I suspect that the issue is with the data. — Shameel
– Shameel, Commented Sep 27, 2019 at 1:12

Laurenz Albe · Accepted Answer · 2019-09-27 04:20:14Z

1

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?

edited Sep 27, 2019 at 4:20

answered Sep 27, 2019 at 4:15

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL match a string from a nested query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related