posted 18 years ago
Hello Ranchers,
I am facing a problem while searching for records in case of Outer Joins. My table designs are as follow.
actors_master
actor_id - int primary key actor_birth_city - int foreign key actor_religion - int foreign key
movies_master
movie_id - int primary key movie_namemovie_release_date - date time movie_language - int foreign key
movies_actor_mappings
movie_id - int foreign key to Movies_Masteractor_id - int foreign key to Actors_Master (Can be null)
Now i want to present a view composing of follwing columns
movie_namemovie_release_datemovie_language actor_birth_cityactor_religion
User should able to search as well sort by movie_release_date, language, actor_birth_city and actor_religion.
So i applied a outer join as :
select movies_master.movie_name, movie_master.movie_release_date, movie_master.movie_language, actors_master.actor_birth_city, actors_master.actor_religion
from movies_master, actors_master, movies_actors_mappings
where
[B]
movies_master.movie_id *= movies_actors_mappings.movie_id and
movies_actors_mappings.actor_id *= actors_master.actor_id
[B]
(Here a *= b means a left outer join b).
This query works fine, it will fetch information about all movies even a movie does not have an actor who is in actors_master table. But the problem starts when I try to seach for movies with particular actor birth city or actor religion. The result consists of the correct searched actots as well the records of movies with no actor (who are in actors_master table) with values of birth city = null and religion = null.
I want to filter such records, and i guess to achieve that using outer join is difficult.
Can anyone show me the way or any alternative?
[The table structures are fictitious but resemble a real business situation which i am facing]
Thanks in advance,