Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Searching results of Outer Join

 
Harshil Mehta
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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_name
  • movie_release_date - date time
  • movie_language - int foreign key


  • movies_actor_mappings
  • movie_id - int foreign key to Movies_Master
  • actor_id - int foreign key to Actors_Master (Can be null)


  • Now i want to present a view composing of follwing columns
  • movie_name
  • movie_release_date
  • movie_language
  • actor_birth_city
  • actor_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,
     
    Vinod Push
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi,

    I think you should go for an if-else structure.
    If actor is entered go for a normal fetch matching that actor else go for an outer-join.

    HTH
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic