Complex Join Problem - Maybe impossible

[Flawless_koder]

Ok - here goes - this has been giving me a headache for a while now:

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'));

Any ideas?

Flawless

[Flawless_koder]

I did try a where also:

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 ambigious username error - it's not intellegent enough to decide that that username should be pulled from the conditon at the end - understandably!

Flawless

[Flawless_koder]

Kind of a solution:

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.

Flawless

[MattR]

Can you explain in english what you are trying to accomplish? that will help a lot since it isn't terribly clear from raw SQL what you're trying to do.

[EvilDoppler]

MattR, don't mind Flawlie...he's on drugs most of the time..uhmmm..that's medication..yeah..that's what i meant...

[Flawless_koder]

Ok ... there are two tables from which a 'member' id can come from - Clients or Servers.
The table they're referenced in has two columns - and to make things easy i called them Client and Server (this table is cs).

Now NORMALLY a Client in the cs table is referencing the clients table.
However - if the status of the row in cs is 'ADMIN' then the Client is actually a server (ie it's an Server to Server conversation - hence ADMIN).

So I need to pull the username of the server and the client out... but when the client is a server, the username needs to come from the Servers table.

To do this I'm currently using:

open $1 FOR 
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')
        );

As you can see - this ultimately returns a refcursor to the Mason backend, and an example might look like:

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

(and you can see how this should come out in my last previous post)

The Mason (perl) which handles this looks like this:

$conn->exec("BEGIN");

my $assignees=$conn->exec("SELECT GetAssigned($user_id,'assignees')");

while (my ($id, $server, $serverb, $client, $status) = $conn->exec("FETCH assignees")->fetchrow){
        my $eClient=$client;
        if ($status eq "ADMIN"){
                $eClient=$server;
                $eClient=$serverb if ($server eq $ENV{REMOTE_USER});
                }
        $sessions{$id}=$eClient;
        }

$conn->exec("COMMIT");

So as you can see - the logic for which the client is - and where the username should be used from - is done serverside - by returning both possiblities then choosing the one that's right client or serverb.

Ideally i'd like the sql to do this...

I hope you get at least some of that Matt

Flawless

[Maelstrom]

I had to do something similar. This may work for you...

USERS
userid
blah...

CONTRACTORS
cid
blah

TASKS
taskid
userid
cid
blah...


This is an overly simple example but what I did was user both columns (cid,userid) when I need the proper information from users I had a userid > 1 in that column. If that column was set to 0 (no regular users) it fetched the info from the contractors column. I did this with a series of left joins. This leaves the emtpy fields null making the table reads very easy.

This allows me to do a very basic if else statement in php to display the correct user information.

[MattR]

I would probably say your tables need redesigning but try this:

-- Grab combo of client username and server username
SELECT cs.id,
       c.username AS Client,
       s.username AS Server
       status
  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.status <> 'ADMIN'
UNION
-- grab combo of servera and serverb username
SELECT cs.id,
       s.username  AS ServerA,
       sb.username AS ServerB,
       status
  FROM current_sessions cs
 INNER JOIN servers s  ON cs.server =  s.id
 INNER JOIN servers sb ON cs.client = sb.id
 WHERE cs.status = 'ADMIN'

I'm not sure what the cs.server = 1 means but I'm sure you can understand where to place that clause in the above two queries.

[MattR]

Usually when I have these sorts of situations I create an extra couple of tables like such:

-- call it anything you want
CREATE TABLE thing(
  row_id SERIAL        PRIMARY KEY,
  name   VARCHAR( 10 ) NOT NULL UNIQUE,
  type   CHAR( 1 )     NOT NULL -- type is 'S' for server, 'U' user, etc.
                                -- if you are really worried about it,
                                -- you can create another table called
                                -- thingtype and make this type column
                                -- a foreign key to thingtype instead.
                                -- I do that for extensability so that if
                                -- I need to add another 'thing' type I can
                                -- simply create another row in thingtype
                                -- and magic happens :)
);

CREATE TABLE server_info(
  server_id     INT      REFERENCES thing( row_id ),
  amount_of_ram SMALLINT NOT NULL,
  etc.
);
  
CREATE TABLE user_info(
  user_id  INT REFERENCES thing( row_id ),
  password CHAR( 32 ) NOT NULL,
  etc.
);

[Flawless_koder]

Thanks Matt - solution worked.

Flawless

[MattR]

Cool, glad I could help!