Select fields from multiple tables

oracleselect

I want to make a query a little complicated, and I'm not able to do it alone.

I have the following tables:

Table color:
id_uo  P
id_cor   PF 
id_area   F 
text_cor

Table area:  
id_area  P
text_area

Table atv:   
id_dest PF
id_cor PF
id_uo PF

Table dest:  
id_dest P
type

What I intend to have as a result is a table that returns the fields as follows:

text_area | id_cor | text_cor | type

Do not know if atv through the table, I can create a query that helps me access to all parameters. Sorry but I'm newbie in the creation of more complex query. I've already tried that with Join and Union but got nothing that I would get. Can you help me?

select * from area:

id_area | text_area
--------------------
  1     |   info1 
  2     |   info2

select * from color:

id_cor |  id_uo | id_area |text_cor
-----------------------------------
  1    |  1222  |    1    |  create
  2    |    1   |    2    |  teste
  2    |  1222  |    1    |  sssss

select * from dest:

id_dest | type
--------------
   1    |   E
   2    |   M

select * from atv:

id_dest | id_cor | id_uo
----------------------------
  1     |   2    |  1222
  2     |   2    |   1
  2     |   1    |  1222

What I want to achieve for each id_uo is the following result (for id_uo: 1222):

text_area | id_cor | text_cor | type
-------------------------------------
 info1    | 1      |  create  |   M
 info1    | 2      |  sssss   |   E

Best Answer

You should be able to get the result that you want by using a LEFT JOIN on each of the tables.

select a."text_area",
  c."id_cor",
  c."text_cor",
  d."type"
from area a
left join color c
  on a."id_area" = c."id_area"
left join atv t
  on c."id_cor" = t."id_cor"
  and c."id_uo" = t."id_uo"
left join dest d
  on t."id_dest" = d."id_dest"
where t."id_uo" = 1222
order by c."id_cor"

See SQL Fiddle with Demo

Result:

| TEXT_AREA | ID_COR | TEXT_COR | TYPE |
----------------------------------------
|     info1 |      1 |   create |    M |
|     info1 |      2 |    sssss |    E |

You will see that when I used the LEFT JOIN on the atv table, I joined it to the color table on both id_cor and id_uo.

Related Question