From: | "Gareth Kirwan" <gbjk(at)thermeoneurope(dot)com> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Complex join query |
Date: | 2002-05-29 13:56:13 |
Message-ID: | 001b01c20718$990f1ff0$55eaa8c0@gbjk1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)thermeoneurope(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | bangh | 2002-05-29 14:35:37 | Re: how to install postgresql!! |
Previous Message | Nick Fankhauser | 2002-05-29 12:23:14 | Re: how to install postgresql!! |