Re: Problems in query - Mailing list pgsql-admin

On Wed, 29 May 2002 10:54:50 -0300
"Denilson Guedes Duarte" <denilson@bnet.com.br> wrote:


Probably this runs, I guess. As for username table, however, I have another
question: why denilson is first.  What rule there would be ?


select c1.cod_cliente,
       c1.controle, c1.dt_vencimento,
       c1.dt_emissao, c1.nro_doc_banco,
       c1.nro_documento, c1.recebido, c1.ref_cidade, c1.valor,
       (select u0.username
          from username u0
         where u0.id = c1.cod_cliente limit 1) as fldusername,
       p1.id, p1.nome, a1.id, a1.nome
from (select c0.*
        from ctareceber c0
       where c0.recebido = 'S'
         and c0.dt_recebimento >= '01/05/2002'
         and c0.dt_recebimento <= '23/05/2002') c1 left join
      username u1 on (u1.id = c1.cod_cliente) left join
      pessoas p1 on (p1.id = c1.cod_cliente) left join
      auxcidades a1 on (a1.id = c1.ref_cidade)



Regard,
Masaru Sugawara


> ok,
> table ctareceber:
> cod_cliente int 4              20
> document varchar 8         NF
> factura int 4                     000001
> ...
>
>
> table cliente:
> cod_cliente int 4            20
> name varchar 60           Company International
>
>
> table username:
> cod_cliente int 4             20                20              20
> 20
> username varchar 8        denilson        deni2          deni3       deni4
>
> in this table have more then one record for client id "20"
>
> I want to do:
>
> print the records from table ctareceber, showing the name of client and the
> first username too. like this:
>
> Factura         Client                                Username
> ------------  --------------------------- -----------
> NF 000001  20  Company International denilson
>
> I used left join because the client it has none record in table username.
>
>
> ----- Original Message -----
> From: "Masaru Sugawara" <rk73@sea.plala.or.jp>
> To: "Denilson Guedes Duarte" <denilson@bnet.com.br>
> Cc: <pgsql-admin@postgresql.org>
> Sent: Wednesday, May 29, 2002 9:17 AM
> Subject: Re: [ADMIN] Problems in query
>
>
> On Sat, 25 May 2002 09:53:39 -0300
> "Denilson Guedes Duarte" <denilson@bnet.com.br> wrote:
>
>
> > and runs ok, but the problem is that the table username (alias u) hane N
> records and I want to get only one record. I try to use a subqyuery:
> > select ....
> > (select u.username from ctareceber c left join username u on
> u.id=c.cod_cliente limit 1) as fldusername
> > .....
> > in this case the query run ok, but I lose the left join property.
> >
> > How I can to do run with left join and get only one record in the other
> table (username u) ?
> >
>
>
> I'm not sure what the problems is.
> Could you show us the output of the result you hope ?
>
>
> Regards,
> Masaru Sugawara
>
>