Complex SELECT (SOLVED)

[Daskalos]

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

thanks in advance

[szlamany]

Could be:

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...

[szlamany]

Maybe this:

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

[Daskalos]

Maybe this:

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:

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
so.. could you explain me how does this "Left Join" works??

[szlamany]

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

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.

[Daskalos]

thanks a lot ;-)