CODEWITHSUNDEEP
oracle-database
Jhon
Jhon

Reputation: 27

Merge table data

Table1               Table2                 Table3           Table4
Sl Name City       index len bre       col tax income      price dicount org
1  ABC   XYZ       1     10   12       1   23   40          1   10      XYZ
2  DEF   asd       2     12   14       2   24   42          2   6       asd
3  ghi   jkl                           3   78   89          3    0      gah

These entries correspond to respective tables. I want to fetch data from all 4 tables irrespective of whether values are present in Table2 or not. Any null value in Table2 should not hamper the output.

select tab1.Name,
        tab2.len,
        tab3.tax,
        tab4.org
From Table1  tab1,
     Table2  tab2, 
     Table3  tab3,   
     Table4  tab4
where tab1.sl=tab2.index(+)
AND      tab2.index(+)=tab3.col
AND   tab3.col=tab4.price;

This query only returns results for those Sl for which there is entry in table 2. How can I resolve this?

Upvotes: 0

Views: 61

Answers (3)

O. Jones
O. Jones

Reputation: 108706

Look, you should move from the 1990s into the early 2000s, by rewriting your query without the 'orrible omega-join (+) syntax.

Converting omega to join, your query comes out like this.

SELECT tab1.Name,
       tab2.len,
       tab3.tax,
       tab4.org
  FROM Table1  tab1,
 left  join  Table2  tab2 ON tab1.sl=tab2.index
 right join  Table3  tab3 ON tab2.index=tab3.col 
 inner join  Table4  tab4 ON tab3.col=tab4.price;

And, then the apparently chaotic combination of right, left, and inner join operations hints at the solution to your problem.

Change over to all left joins and your Table1 rows won't be suppressed when they don't match other tables.

      SELECT tab1.Name,
             tab2.len,
             tab3.tax,
             tab4.org
        FROM Table1 tab1
   LEFT JOIN Table2 tab2 ON tab1.sl=tab2.index
   LEFT JOIN Table3 tab3 ON tab2.index=tab3.col 
   LEFT JOIN Table4 tab4 ON tab3.col=tab4.price;

Even if you must use the old omega join syntax, you should use it in a way which won't suppress rows from Table1 

select tab1.Name,
        tab2.len,
        tab3.tax,
        tab4.org
From Table1  tab1,
     Table2  tab2, 
     Table3  tab3,   
     Table4  tab4
where tab1.sl=tab2.index(+)
AND   tab2.index=tab3.col(+)
AND   tab3.col=tab4.price(+);

The position of the (+) on the right means it's a left join, and vice versa.

Upvotes: 0

CLAbeel
CLAbeel

Reputation: 1088

To use a proper ANSI left join:

select tab1.Name,
        tab2.len,
        tab3.tax,
        tab4.org
From Table1  tab1  
     inner join Table3  tab3 on tab1.sl.tab3.col
     inner join Table4  tab4 on tab3.col=tab4.price
     left join Table2  tab2 on tab1.sl=tab2.index;

This makes your code much more readable.

Upvotes: 1

meet
meet

Reputation: 166

Try following ---

select tab1.Name,
        tab2.len,
        tab3.tax,
        tab4.org
From 
   Table1  tab1 left join Table2  tab2  
on tab1.sl=tab2.index(+) join Table3  tab3 
on tab2.index(+)=tab3.col join Table4  tab4 
on tab3.col=tab4.price;

Upvotes: 0

Related Questions