• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Searching results of Outer Join

 
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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,
     
    Greenhorn
    Posts: 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • 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
     
    Consider Paul's rocket mass heater.
    reply
      Bookmark Topic Watch Topic
    • New Topic