Results 1 to 6 of 6

Thread: Joining Two Tables and Retriving Data

  1. #1
    Junior Member
    Join Date
    Nov 2005
    Answers
    17

    Joining Two Tables and Retriving Data

    Hi all

    I have two tables, one is category and another is articles,
    For each category i have many articles in article table,
    The articles table is related to category table via cat_id field,
    I need to show a list of categories along with related articles,
    I am able to retrieve records successfully from the tables the issue now is,
    For a few categories there are no articles, I need to retrieve even such categories too!!

    Currently I am able to retrieve data only for those categories which have related articles.
    Question is How to retrieve category names even if there are no related articles

    Any helping soul.....


  2. #2
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Joining Two Tables and Retriving Data

    Hi

    Outer joins serve your requirement. An outer join extends the result of a simple join. It returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

    Here is the oracle query that fetches all the records in your CATERGORIES table including all the categories do not have related records in ARTICLES table,

    Select * from categories c,articles a where c.cat_id = a.cat_id(+);

    Hope this helps you...

    Thanks,
    James


  3. #3
    Junior Member
    Join Date
    Nov 2005
    Answers
    17

    Re: Joining Two Tables and Retriving Data

    Hi James

    Thanks for your timely hint.
    Actually I am working on MySQL, I am sure the SQL Query concept is same across database packages.

    Based on your suggestion, I played around the query and finally made it possible to retrieve all Categories irrespective of whether Articles tables has related records or not.

    One thing to note here is, the query made use of LEFT join to retrieve all records from Categories and only the related records from Articles table.

    Does that means by using RIGHT join I can retrieve all Articles table including those that do not have Categories mentioned.

    I still have to experiment with OUTER join then... How exactly will the output be by using OUTER join. How different it is from LEFT join and RIGHT join.

    Way to learn...
    Thanks Again!!!


  4. #4
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Joining Two Tables and Retriving Data


    Does that means by using RIGHT join I can retrieve all Articles table including those that do not have Categories mentioned.


    Yes. Right outer join retrieve all Articles including those that do not have Categories.

    Thanks,
    James.


  5. #5
    Junior Member
    Join Date
    May 2008
    Answers
    2

    Re: Joining Two Tables and Retriving Data

    hi this is adi

    i think left outer join may help u to get all the articles......inthis the + symbol comes at right side by this u can get all the articles that do not have any categories ..........

    by using right outer join u can get all the categories that do not have any related articles...


  6. #6
    Junior Member
    Join Date
    May 2008
    Answers
    7

    Re: Joining Two Tables and Retriving Data

    Use Right Outer Join.U get the result


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact