CODEWITHSUNDEEP
oracle-database
senthil_m01
senthil_m01

Reputation: 21

Select data from multiple tables even if there no data in one table

I have 6 tables as follows,

table-1, table-2, table-3, table-4, table-5, table-6

All the tables have the same primary key.

I want to create a view out of all these base tables.

The where condition of the select query should qualify the primary keys of the first five tables.

Requirement is this,

1.The view should fetch the data from the first five tables plus the data in the sixth table. 2.If there is no data in the 6th table, the view should still display the data in the 5 tables.

I can qualify the primary keys of all the tables, but if i did then the view wont display any data when there is no data in the 6th table and i dont want that.

what do i do now?

Upvotes: 2

Views: 4295

Answers (3)

Alain Pannetier
Alain Pannetier

Reputation: 9514

Are you referring to an outer join ?

Something like the following ?

where 
    t1.id = t2.id and
    t2.id = T3.id and
    ...
    t5.id = t6.id (+)

Upvotes: 1

vktl
vktl

Reputation: 36

Select t1.*, -- put here columns you need
       t2.*,
       t3.*,
       t4.*,
       t5.*,
       t6.*
  From table1 t1
       Join table2 t2 On t1.pfield = t2.pkfield
       Join table3 t3 On t1.pfield = t3.pkfield
       Join table4 t4 On t1.pfield = t4.pkfield
       Join table5 t5 On t1.pfield = t5.pkfield
  Left Join table6 t6 On t1.pfield = t6.pkfield

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425421

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.t1_id = t2.id
…
LEFT JOIN
        table6 t6
ON      t6.t5_id = t5.id

Upvotes: 6

Related Questions