Complex SELECT (SOLVED)

Hi There
take a look at the image below:

well
I need to select everything from table Pessoa where idUnidade from table Unidade be equal to 17

if somebody understands my question please help me :cry:

thanks in advance

Could be:

Code:

---

Select * From Pessoa
  Where idArea in (Select idArea From Area Where idUniDade=17)

---

[edit] oops - wait a minute - that's not right - give me a second...

Maybe this:

Code:

---

Select * From Pessoa
  Where idPessoa in (Select idPessoa From AreaPessoa
                            Left Join Area on Area.idArea=AreaPessoa.idArea
                            Where idUniDade=17)

---

Quote:

---

Originally Posted by szlamany

Maybe this:

Code:

---

Select * From Pessoa
  Where idPessoa in (Select idPessoa From AreaPessoa
                            Left Join Area on Area.idArea=AreaPessoa.idArea
                            Where idUniDade=17)

---

---

hum.. it worked.. i after I saw you first post, i did this:

Code:

---

Select * From Pessoa
  Where idpessoa in (Select idpessoa From AreaPessoa Where idarea in
(SELECT idarea FROM area where idunidade = 17))

---

i don't know why, but i think you code is better :bigyello:
so.. could you explain me how does this "Left Join" works??

Not knowing much about the data in your tables - it's probably better yet to do this:

Code:

---

Select * From Pessoa
  Where idPessoa in (Select idPessoa From AreaPessoa
                            Left Join Area on Area.idArea=AreaPessoa.idArea
                            Where idUniDade=17
                            Group by idPessoa)

---

The GROUP BY will make sure that the "sub-query" recordset is as small as possible.

The LEFT JOIN is creating a "single working table" from both the idPessoa and Area tables - all columns from both tables will be JOINED as if one table - on the criteria shown in the ON clause of the LEFT JOIN.

I use LEFT JOIN instead of JOIN as a habit since we have so much legacy data that we've converted from mainframes. The relationships are not always clean. JOIN will throw away the "primary" table row (in the FROM clause) if the JOIN table sister row does not exist. LEFT JOIN will not do that - the "primary" table row comes through, but the columns from the JOINED table for that row will be NULL.

thanks a lot ;-)