Problem with a JOIN

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 27 Feb 2008 5:56 pm

Guys, I need to do a select to return the following information together: UF_NASC and UF_CARTORIO these fields are in the same table as: cod_munic_nasc and cod_munic_cartorio--the problem is that table the two field are foreign keys to the same field that is cod_municipio--table B I'm not knowing make this join, because I will use the same field for two different things, see below: First I made an appointment with the codes that are different then he called me back that aí
Code: Select all
SQL> select A.cod_munic_nasc, A.cod_munic_cartorio
  2  from dependentes A, municipio B
  3  where A.cod_munic_nasc = B.cod_municipio
  4  and A.cod_munic_cartorio = B.cod_municipio
  5  and rownum <=5;
       2800308            2800308
       2800308            2800308
       2800308            2800308
       2800308            2800308
       2800308            2800308
aí I thought, \"will see the UF that the guy was born is the same as the record of the registry\", in this case the select above was right!
aí did the test to see if you had different record us UFs
Code: Select all
SQL> select cod_munic_nasc, cod_munic_cartorio
  2  from dependentes
  3  where cod_munic_nasc <> cod_munic_cartorio
  4  and rownum <=5;
       2408102            2800308
       2802908            2806008
       2802809            2800308
       2800407            2804805
       2804003            2800308
Then I note that has just in case the UF of birth does not equal UF the registry, then put a condition on the 1° select, that looked like this:
Code: Select all
SQL> select A.cod_munic_nasc, A.cod_munic_cartorio
  2  from dependentes A, municipio B
  3  where A.cod_munic_nasc = B.cod_municipio
  4  and A.cod_munic_cartorio = B.cod_municipio
  5  and A.cod_munic_nasc <> A.cod_munic_cartorio
  6  and rownum <=5;

não há linhas selecionadas
need I say more??????

I don't know if they understand, but I'm waiting for reply!

Thanks
tatianeweb
Location: ARACAJU-SE

Tati

Poston Wed, 27 Feb 2008 8:28 pm

If I understand correctly, your problem is in the fact that you are using the table municipio only once.
As there are two distinct fields in your table dependent that reference the field cod_municipio , you need to use it twice, to perform the join on each of the fields:
Code: Select all
select d.cod_munic_nasc, d.cod_munic_cartorio
from   dependentes d
     , municipio m_nascimento -- Município de nascimento
     , municipio m_cartorio   -- Município de cartório
where  d.cod_munic_nasc     = m_nascimento.cod_municipio
and    d.cod_munic_cartorio = m_cartorio.cod_municipio
and    rownum <=5
;
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Thu, 28 Feb 2008 10:25 am

Thread moved to SQL forum
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 28 Feb 2008 2:51 pm

Work what you showed me, but I don't quite understand how it worked this select, so I didn't understand that when I went to adapt to another field that needed gave error!

with what you told me I tested it and gave it:
Code: Select all
SQL> select d.cod_munic_nasc, d.cod_munic_cartorio
  2  from   dependentes d, municipio m_nascimento, municipio m_cartorio
  3  where  d.cod_munic_nasc     = m_nascimento.cod_municipio
  4  and    d.cod_munic_cartorio = m_cartorio.cod_municipio
  5  and    d.cod_munic_nasc != d.cod_munic_cartorio
  6  and    rownum <=5;

COD_MUNIC_NASC COD_MUNIC_CARTORIO
-------------- ------------------
       2408102            2800308
       2802908            2806008
       2802809            2800308
       2800407            2804805
       2804003            2800308
aí beleza, then I took the code fields of 1° line and substitute the field of acronym of uf that stays on the table municipio , except that the result is very strange. See how it looks!
Code: Select all
SQL> select m_nascimento.sgl_uf Nasc, m_cartorio.sgl_uf Cart
  2  from   dependentes d, municipio m_nascimento, municipio m_cartorio
  3  where  d.cod_munic_nasc     = m_nascimento.cod_municipio
  4  and    d.cod_munic_cartorio = m_cartorio.cod_municipio
  5  and    d.cod_munic_nasc != d.cod_munic_cartorio
  6  and    rownum <=5;

NA CA
-- --
RN SE
SE SE
SE SE
SE SE
SE SE

if the error is not of logic or syntax, just know that ta wrong!
And aí? What do I do now??? What was the error???
tatianeweb
Location: ARACAJU-SE

Tati

Poston Thu, 28 Feb 2008 6:25 pm

Hmm.. I'm not seeing the problem.

The query is returning the State of birth and registry of each dependent that does not have the same municipality of birth and registry.

Just in doubt, to confirm, try to list all the fields involved to give a look:
Code: Select all
select d.cod_munic_nasc            dep_municipio_nascimento
     , m_nascimento.cod_municipio  mun_municipio_nascimento
     , d.cod_munic_cartorio        dep_municipio_cartorio
     , m_cartorio.cod_municipio    mun_municipio_cartorio
     , m_nascimento.sgl_uf         sgl_uf_nascimento
     , m_cartorio.sgl_uf           sgl_uf_cartorio
from   dependentes d
     , municipio m_nascimento
     , municipio m_cartorio
where  d.cod_munic_nasc     = m_nascimento.cod_municipio
and    d.cod_munic_cartorio = m_cartorio.cod_municipio
and    d.cod_munic_nasc    != d.cod_munic_cartorio
and    rownum <=5;
If I misunderstood you, explain a little better what you expect to receive as a result.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 29 Feb 2008 7:20 am

OK, it was me that I'm messing up the logic of the problem thanks for your help!
A hug
tatianeweb
Location: ARACAJU-SE

Tati


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests