This week's giveaway is in the Programmer Certification (OCPJP) forum.
We're giving away four copies of Java Mock Exams (software) and have David Mayer on-line!
See this thread for details.
Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) 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.

  • 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
    movies_master.movie_id *= movies_actors_mappings.movie_id and
    movies_actors_mappings.actor_id *= actors_master.actor_id

    (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
    Posts: 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    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.

    Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
    the new thread boost feature: great for the advertiser and smooth for the coderanch user
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!