Обсуждение: 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
tryto 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

Hello all,
    Let's dive straight in, eh?

1.
    Table: current_sessions
    Fields: Server (int Foreign Key servers.id)
    Client (int Foreign Key clients.id)
    status (vc(10))

2.
    Table: clients
    Fields: id (serial Pkey)
    username (UNIQUE)

3.
    Table: servers
    Fields: id (serial Pkey)
    username (UNIQUE)

Normally, the username of the client should come from the clients table,
hence you'd inner join plainly.
IF the status is 'ADMIN' however - it means both the server and the client
come from the servers table.

HOW ( if at all ) can I pull this off in a join without having to
restructure the tables?
This is what I did have:

SELECT cs.id, s.username AS Server, c.username AS
Client from current_sessions cs INNER JOIN clients c ON (cs.client=c.id)
INNER JOIN servers s ON (cs.server=s.id) where cs.status!='AVAIL' AND
((cs.server=1) OR (cs.client=1 AND cs.status='ADMIN'));

but that obviously wouldn't work!!!
SO, I tried a where version:

SELECT cs.id, s.username, username from current_sessions cs, clients c,
servers s
where ((cs.status=='AVAIL') AND (cs.client = c.id)) OR ((cs.status=='ADMIN')
AND (cs.client = s.id));

but this obviously returns an ambiguous username error - it's not
intelligent enough to decide that that username should be pulled from the
condition at the end - understandably!

AND FINALLY:

Kind of a solution:
code:
----------------------------------------------------------------------------
----
SELECT
      cs.id,
      s.username AS ServerA,
      sb.username AS ServerB,
      c.username AS Client,
      cs.status
 from current_sessions cs
   INNER JOIN clients c ON (cs.client=c.id)
   INNER JOIN servers s ON (cs.server=s.id)
   INNER JOIN servers sb ON (cs.client=sb.id)
 where   cs.status != 'AVAIL'
     AND
        (
         (cs.server=1)
        )
     OR
        (
         (cs.client=1 AND cs.status='ADMIN')
        );
----------------------------------------------------------------------------
----

And this produces:

    id  | servera | serverb | client | status
    ----+---------+---------+--------+-------
    645 | pco     | rrg     | mpo    | CONV
    650 | rrg     | pco     | gbjk   | ADMIN

Which then gives:

    id  | Server | client
    ----+---------+------
    645 | pco     | mpo
    650 | rrg     | pco

because NORMALLY the client is the client -
BUT if the status is ADMIN then the client is the second server.

Now this is as close as I can get it...
I can use the server side language to handle the output
from this... but bearing in mind that I'm using
Postgresql Functions (plpgsql) - and opening a cursor for this select before
returning it ..

Is there anything I can do after this to make the Second output table i've
shown (id, server, client) from the first one (id, server, serverb, client,
status) given the clause that the client of the first table should be the
client in the second - UNLESS
the status of the first table is ADMIN - in which case the client of the
second is the serverb from the first.

make sense?

i hope so.

Thanks everyone

Gareth Kirwan
Programming & Development
Thermeon Europe Ltd
+44 1293 864 303
gbjk@thermeoneurope.com

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
>
>

On Wed, 29 May 2002, Gareth Kirwan wrote:

> Hello all,
>     Let's dive straight in, eh?
>
> 1.
>     Table: current_sessions
>     Fields: Server (int Foreign Key servers.id)
>     Client (int Foreign Key clients.id)
>     status (vc(10))
>
> 2.
>     Table: clients
>     Fields: id (serial Pkey)
>     username (UNIQUE)
>
> 3.
>     Table: servers
>     Fields: id (serial Pkey)
>     username (UNIQUE)
>
> Normally, the username of the client should come from the clients table,
> hence you'd inner join plainly.
> IF the status is 'ADMIN' however - it means both the server and the client
> come from the servers table.

If you're actually using foreign keys for Client, you may have some other
problems with that structure since it seems like you've got a key pointing
off into the wrong table when status is ADMIN.

> HOW ( if at all ) can I pull this off in a join without having to
> restructure the tables?

I'd suggest using UNION ALL.
 Get all the non admin ones with a join on client
UNION ALL
 Get all the admin ones with another join on server