Self Doubt

Question

I want to join the two tables of movies and actors and then output only those results that have gender =”F”, can you tell me where I am going wrong?

Comments

What happens when you give a.gender != ‘M’?

Meanwhile the join condition is bogus here as actor ID and movie ID do not seem to have any relation (female movies?)

---

Arjun sir, I have not tried that statement, will do it.

Meanwhile the join condition is bogus here as actor ID and movie ID do not seem to have any relation (female movies?)

But if it didn’t have any relationship, then it should have returned an empty set for males too?

There is another relation called roles which consist of movie id, actor id, and role. Do I need to join that relation too to filter all movies that are played by a female actor?

---

Hii @tusharb    

Use this query

select * from movies where id=ANY(select id from actors where gender='F');

you will get no common id present b/w movies and actors table when you are selecting gender as Female.

 

 

Answer 1

The code syntax is correct.However,You need to analyze the Data Storage Structure for the IMDB database.

In the imdb.sql link you shared,with reference to the question

There are 412,320 movies in the movies table of the database sorted A-Z alphabatically.

 

Image link : https://drive.google.com/file/d/12rhdAbiabhvU1J4XqodEppkCy1RKCp9n/view?usp=sharing

Actors : There are 845,465 actors

Image link : https://drive.google.com/file/d/1HZDq7yAiFww5VsObs9zYbS5OMerqeraF/view?usp=sharing

But,the interesting part is ,it first displays Male in A-Z order,and then,the female actresses,however,starts from 528,787

Image Link : https://drive.google.com/file/d/1KqXcqR30IN8QtNYOQFhJO8vqqRraydsZ/view?usp=sharing

So,till the last value of m.id which is 412,320 will also have Male as Gender and so,your join returned empty set.

Image link : https://drive.google.com/file/d/1Hoh05hfAw9E_av2aV25X0-Jkqo_R5Cwp/view?usp=sharing

Hence,Your Command worked for Male Gender but not for Female.

Comments

Your pictures are too small to see, can you change them?

---

I added Google Drive links for images.The Website compressed the original ones.