Results 1 to 7 of 7

Thread: SQL Query

  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: SQL Query

    hi,

    I have the following table:

    user(id, name)
    branch(id, manager, rep, owner)

    the columns manager, rep and owner in branch table are referenced to id's from the user table.

    I need to write a query to get id, and names of the three users instead of their user id's.

    Any help would be gr8! thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    This looks like home work and I doubt you have a table named "user".

    PHP Code:
    SQLCREATE TABLE USER(id NUMBERname VARCHAR2(10));
    CREATE TABLE USER(id NUMBERname VARCHAR2(10))
                 *
    ERROR at line 1:
    ORA-00903invalid table name 
    In short the answer is to use table aliases. Convince me it is not home work and I will supply the detailed answer.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    select u1.name as manager, u2.name as rep, u3.name as owner
    from branch b, user u1, user u2, user u3
    where u1.id = b.manager and u2.id = b.rep and u3.id = b.owner


    -cf

  4. #4
    Join Date
    Jan 2004
    Posts
    7
    well ... u guessed it. I do not have a table name user! but due to privacy arrangements with my client, i cannot share any info of my project on an open forum, including table names! So I had to make up a situation similar to mine!

    thanks for your help btw.

  5. #5
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    Actually FYI I just remembered you can have a table named user by using quotes.

    SQL> CREATE TABLE "USER"(id NUMBER, name VARCHAR2(10));

    Table created.

    re your orignal request.

    PHP Code:
    SQL
    CREATE TABLE USERT(id NUMBERname VARCHAR2(10));

    Table created.

    SQL
    CREATE TABLE BRANCH(id NUMBERMANAGER NUMBERREP NUMBEROWNER NUMBER);

    Table created.

    SQLINSERT INTO USERT VALUES(1'BOB');

    1 row created.

    SQL
    INSERT INTO USERT VALUES(2'SUE');

    1 row created.

    SQL
    INSERT INTO USERT VALUES(3'JOHN');

    1 row created.

    SQL
    INSERT INTO USERT VALUES(4'JENNY');

    1 row created.

    SQLSELECT FROM USERT;

            
    ID NAME
    ---------- ----------
             
    1 BOB
             2 SUE
             3 JOHN
             4 JENNY


    SQL

    INSERT INTO BRANCH VALUES(1,1,2,3);

    1 row created.

    SQL
    INSERT INTO BRANCH VALUES(2,4,1,3);

    1 row created.

    SQLCOMMIT;

    Commit complete.

    SQLSELECT B.IDM.NAME AS MANAGERR.NAME AS REPO.NAME AS OWNER
      2  FROM BRANCH B
    USERT MUSERT RUSERT O
      3  WHERE B
    .MANAGER M.ID
      4  
    AND B.REP R.ID
      5  
    AND B.OWNER O.ID;

            
    ID MANAGER    REP        OWNER
    ---------- ---------- ---------- ----------
             
    1 BOB        SUE        JOHN
             2 JENNY      BOB        JOHN

    SQL


  6. #6
    Join Date
    Jan 2004
    Posts
    492
    True you can, but you would never want to do so in my opinion. Why go through the extra effort to mask the reserved keywords instead of just adding an underscore and an extra description?

  7. #7
    Join Date
    Jan 2004
    Posts
    7
    hi,

    thanks for all your help. the queries work fine, but wouldnt return any rows containing a null in one of the names?

    thanks!

Posting Permissions

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