Complex join query - Mailing list pgsql-admin

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