Select, several related code field in a single table

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

Poston Thu, 10 Jan 2008 5:07 pm

Hello guys, I have a little problem here ... I have a table like this (Oracle)-FIELD records-table-TYPE code---number resp1----number resp2----resp3----number number date-----date and another user so-people table FIELD-----coduser----TYPE number login--------varchar2 (10) password-------varchar2 (300) Dae want to do a select on registration table for a particular date, but I want him to call me back all the names of those responsible, who are in the users table.


the field resp2 and resp3, resp1 are all filled the problem I do not know how to make a select to return the 3 names at the same time ... If you do. ..

Code: Select all
select registro.id, usuarios.login, registro.resp2, registro.resp3 from registro, usuarios where registro.resp1 = usuarios.coduser
or else could do so but returns multiple rows.


Code: Select all
select registro.id, usuarios.login from registro, usuarios where registro.resp1 = usuarios.coduser
union
select registro.id, usuarios.login from registro, usuarios where registro.resp2 = usuarios.coduser
union
select registro.id, usuarios.login from registro, usuarios where registro.resp1 = usuarios.coduser
the problem with this last is that returns multiple rows ... type 1-danilo 1-carlos 1-2 2-andre luiz-danilo 2-junior and would like so-1 fiasse danilo-Charles-andre 2-luiz-danilo-junior has how to do this???

Thanks again
dboss
Location: São José - SC

Poston Fri, 11 Jan 2008 7:21 am

Brother, do a test there, see if this code below will help you.
Code: Select all

SELECT r.cod,
        u.nome resp1,
        u1.nome resp2,
        u2.nome resp3
   FROM usuarios  u,
        usuarios u1,
        usuarios u2,
        registro  r
  WHERE u.cod = r.resp1
    AND u1.cod = r.resp2
    AND u2.cod = r.resp3
  ORDER BY r.cod;
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Fri, 11 Jan 2008 2:18 pm

dae ... beauty ... thanks for your help EEAA.

Perfect great .... thanks cheezburger network big hug bross
dboss
Location: São José - SC


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests