Re: Problems in query

On Wed, 29 May 2002 10:54:50 -0300
"Denilson Guedes Duarte" <denilson(at)bnet(dot)com(dot)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(at)sea(dot)plala(dot)or(dot)jp>
> To: "Denilson Guedes Duarte" <denilson(at)bnet(dot)com(dot)br>
> Cc: <pgsql-admin(at)postgresql(dot)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(at)bnet(dot)com(dot)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
>
>