SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  2. #2
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  3. #3
    Perl/Mason Guru Flawless_koder's Avatar
    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
    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
    Last edited by Flawless_koder; May 29, 2002 at 04:35.
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  4. #4
    Database Jedi MattR's Avatar
    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.

  5. #5
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    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

  6. #6
    Perl/Mason Guru Flawless_koder's Avatar
    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')
            );
    As you can see - this ultimately returns a refcursor to the Mason backend, and an example might look like:
    Code:
    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:

    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");
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  7. #7
    What? Maelstrom's Avatar
    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.

  8. #8
    Database Jedi MattR's Avatar
    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'
    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.

  9. #9
    Database Jedi MattR's Avatar
    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.
    );

  10. #10
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  11. #11
    Database Jedi MattR's Avatar
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •