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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 

Table area:  
id_area  P

Table atv:   
id_dest PF
id_cor PF
id_uo PF

Table dest:  
id_dest P

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
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",
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


|     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

Your Answer


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.