Forums Register Login

Searching results of Outer Join

+Pie Number of slices to send: Send
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,
    +Pie Number of slices to send: Send
    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
    This parrot is no more. It has ceased to be. Now it's a tiny ad:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com


    reply
    reply
    This thread has been viewed 921 times.
    Similar Threads
    Restore problem
    SEVERE: Duplicate entry '1' for key 'PRIMARY'
    Beans binding with local variable
    Dynamic table selects
    one-to-many relation
    More...

    All times above are in ranch (not your local) time.
    The current ranch time is
    Mar 28, 2024 04:51:43.