Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Could you show us a few rows of sample data and the desired output? And possibly the query you have so far. With these you could be more sure that you'd get the answer you want. –  dezso Nov 27 '12 at 11:33
    
hope you explained everything to help you. –  zmki Nov 27 '12 at 13:06
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
Exactly, what has walked laps here and never did the query that way. thank you very much –  zmki Nov 27 '12 at 13:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.