How to join 4 tables in oracle?

[amithlaxman]

Hi,

I have four tables tab1,tab2,tab3 & tab4

#contents of tab1:
Id   dataname
222  perl

#contents of tab2:
Id   data_desc
222  perl is a scripting language

#contents of tab1:
Id   other_desc
222  Regular expression

#contents of tab1:
Id   date
222  22-33-1964

I want to join all the 4 tables.

I tried like this:

select a.Id,b.data_desc,c.other_desc,d.date from tab1 a,tab2 b,tab3 c,tab4d where a.Id=b.Id and b.Id = d.Id and c.Id=a.Id;

This is the output

Id  dataname data_desc                     other_desc          date
222 Perl     perl is a scripting language  Regular expression  22-33-1964

How to join all the 4 tables and get all the values with out any duplicacy?

No duplicate record should be there.

Regards
Amith

[debasisdas]

the code will be more readable this way

SELECT a.Id,b.data_desc,c.other_desc,d.DATE 
FROM tab1 a,tab2 b,tab3 c,tab4 d 
WHERE a.Id = b.Id 
AND a.Id = c.Id 
AND a.Id = d.Id;

NOTE:- avoid using DATE as a field name as it is a predefined key word (data type).

which record is duplicate in the query result ?

Adding the DISTINCT keyword will eliminate any duplicates.
Please remember that your D.Date column may contain a time component,
so it may display as duplicate records, when they are not.
You may want to do a 'To_Char (D.Date, <format model>)' or a 'Trunc (D.Date)'.

SELECT DISTINCT a.Id,b.data_desc,c.other_desc,d.DATE 
FROM tab1 a,tab2 b,tab3 c,tab4 d 
WHERE a.Id = b.Id 
AND a.Id = c.Id 
AND a.Id = d.Id;