I have this query:
select c.cod_cliente, c.controle, c.dt_vencimento, c.dt_emissao, c.nro_doc_banco,
c.nro_documento, c.recebido, c.ref_cidade, c.valor,
(select u.username from ctareceber c left join username u on u.id=c.cod_cliente limit 1) as fldusername, p.id, p.nome, a.id, a.nome from (((ctareceber c left join username u on u.id=c.cod_cliente) left join pessoas p on p.id=c.cod_cliente) left join auxcidades a on a.id=c.ref_cidade) where c.dt_recebimento>='01/05/2002' and c.dt_recebimento<='23/05/2002' and c.recebido='S'
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) ?
Denilson Guedes Duarte
Provedor de Internet da Fronteira Oeste Ltda - ISPbnet
Fone: (55) 411 6558
Cel.: (55) 9973 0219
Email: denilson@bnet.com.br
ICQ: 14319874