SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
May 29, 2002, 04:38 #1
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Complex Join Problem - Maybe impossible
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---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 29, 2002, 04:49 #2
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 29, 2002, 05:10 #3
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
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.
FlawlessLast edited by Flawless_koder; May 29, 2002 at 05:35.
---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 29, 2002, 08:10 #4
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
May 29, 2002, 17:25 #5
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
MattR, don't mind Flawlie...he's on drugs most of the time..uhmmm..that's medication..yeah..that's what i meant...
* Due to the last tax raise our prices has risen
* - answers w/o thought $1 - answers w/ thought $3
* - correct answers $10
* - Dumb stares are still free
-
May 30, 2002, 02:05 #6
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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') );
Code:id | servera | serverb | client | status ----+---------+---------+--------+------- 645 | pco | rrg | mpo | CONV 650 | rrg | pco | gbjk | ADMIN
The Mason (perl) which handles this looks like this:
Code:$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");
Ideally i'd like the sql to do this...
I hope you get at least some of that Matt
Flawless---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 30, 2002, 11:06 #7
- Join Date
- Oct 2001
- Location
- Whistler BC originally from Guelph Ontario
- Posts
- 2,175
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Maelstrom Personal - Apparition Visions
Development - PhP || Mysql || Zend || Devshed
Unix - FreeBSD || FreeBsdForums || Man Pages
They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.
-
May 30, 2002, 11:08 #8
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I would probably say your tables need redesigning
but try this:
Code:-- 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'
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
May 30, 2002, 11:16 #9
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Usually when I have these sorts of situations I create an extra couple of tables like such:
Code:-- 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. );
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
May 31, 2002, 05:52 #10
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Matt - solution worked.
Flawless---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 31, 2002, 06:07 #11
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Cool, glad I could help!
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
Bookmarks