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.....

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,

[B][FONT="Verdana"]Select * from categories c,articles a where c.cat_id = a.cat_id(+);[/FONT][/B]

Hope this helps you...

Thanks,
James

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!!!

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

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

Thanks,
James.

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...

Use Right Outer Join.U get the result