#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3

    Query Data From Two Tables in SQL Statement


    OK I have my 'employees.dept' field type set to INTEGER but I'm still
    trying to understand how I can perform a SQL statement that will allow
    me to query the 'employees' table and visibly see employees.manager =
    'Phill Collins' rather than it's assigned numerical INTEGER. Below are
    both tables described:

    Code:
                                   Table "public.managers"
     Column |         Type          |                       Modifiers
    --------+-----------------------+-------------------------------------------------------
     id     | integer               | not null default
    nextval('managers_id_seq'::regclass)
     name   | character varying(50) | not null
     email  | character varying(50) | not null
     dept   | integer               |
     salary | numeric(8,2)          | not null
     hire   | date                  | not null
    Indexes:
       "managers_pkey" PRIMARY KEY, btree (id)
       "managers_email_key" UNIQUE CONSTRAINT, btree (email)
    
    
                                    Table "public.employees"
     Column  |         Type          |                       Modifiers
    ---------+-----------------------+--------------------------------------------------------
     id      | integer               | not null default
    nextval('employees_id_seq'::regclass)
     fname   | character varying(50) | not null
     lname   | character varying(50) | not null
     email   | character varying(50) | not null
     dept    | integer               |
     manager | integer               |
     salary  | numeric(8,2)          | not null
     hire    | date                  | not null
    Indexes:
       "employees_pkey" PRIMARY KEY, btree (id)
       "employees_email_key" UNIQUE CONSTRAINT, btree (email)
    Is there a way I can query the employees table and have the SQL
    statement resolve the INTEGER value from 'employees.manager' to
    display the data in 'managers.name'?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select employees.fname,
           employees.lname,
           managers.name
      from employees
      join managers
        on employees.manager = managers.id

    Comments on this post

    • CarlosinFL agrees : Perfect
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3
    That worked great! Thank you. I tried to also build on to this SQL statement but it appears I did it wrong:

    Code:
    SELECT 
    employees.fname AS First_Name,
    employees.lname AS Last_Name,
    employees.dept AS Dept,
    managers.name AS Manager,
    FROM employees
    JOIN managers
    ON employees.manager = managers.id
    AND employees.dept = dept.name;
    I also want to show the data from 'dept.name' table since the records for employee.dept are INTEGER types. How can I alter this statement to also grab the referenced INTEGER from employee.dept and reference the dept.name field?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    I would do this a bit differently, but its the same idea with the natural join. It might be a little more obvious what is happening if it's written this way, though.

    I believe this will work. It displays the tuples from the manager and employee tables where the manager ID is the same as the employee manager. It's the same code as a join on or natural join.

    Code:
    SELECT E.firstName as First_Name, E.lastName as Last_Name, E.dept as Department, M.managers.name as Manager
    FROM exployees E, managers M
    WHERE E.manager = M.id
    I'm not really sure what you are asking with the department name. The above will display the department name in addition to everything else.. Joins are normally done on primary keys. I'm a little confused as to what the relation is between your two entities. Could you elaborate a bit on their cardinality and relation?

    Cheers, Cam.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3
    Originally Posted by Cameron0960

    I'm not really sure what you are asking with the department name. The above will display the department name in addition to everything else.. Joins are normally done on primary keys. I'm a little confused as to what the relation is between your two entities. Could you elaborate a bit on their cardinality and relation?

    Cheers, Cam.
    It's rather simple but I don't know how logical this is since I'm not a "real" DBA. Feel free to advise me if this isn't practical or if there's a more common (DBA) way of doing what I'm trying to do.

    1. I have a database with 3 tables:

    - dept
    - employees
    - managers

    Now w/o describing all three tables in detail, I will say that the dept table has a field called 'manager' which takes INTEGER values. Those values in dept.manager should match up to the managers.id (PRIMARY KEY) in the 'managers' table.

    Employees also has a field called employees.manager & employees.dept which also has an INTEGER data type. I want to perform a SELECT query on the employees table that show me the actual dept.name and manager.name rather than there stored numerical values as this wont mean anything to the person unless they have to then find out that dept.id value 6 is dept.name 'Legal'. I don't know if what I tried to explain above made any sense...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3
    Just to clarify what I'm trying to do...I used a Graphical Query Building in a SQL API I had and rather than getting this result:

    Code:
    zoo=# SELECT * FROM employees;
     id | fname  |  lname  |      email       | dept | manager |  salary  |    hire    
    ----+--------+---------+------------------+------+---------+----------+------------
      1 | Carlos | Mennens | cmennens@zoo.com |    4 |       4 | 89001.25 | 2006-02-01
      2 | Ryan   | Smith   | rsmith@zoo.com   |    2 |       2 | 78212.32 | 2009-09-22
      3 | Roger  | Dodd    | rdodd@zoo.com    |    1 |       1 | 87931.94 | 2008-01-08
      4 | David  | Smith   | dsmith@zoo.com   |    2 |       2 | 76419.21 | 2009-05-24
      5 | Smith  | Fields  | sfields@zoo.com  |    3 |       3 | 98212.97 | 2010-07-01
    You can see above that whom ever runs this statement still has no idea who Carlos' dept or manager is w/o doing more work. But the API suggested the following SQL statement:

    Code:
    SELECT 
      employees.id AS "Emp_ID", 
      employees.fname AS "First", 
      employees.lname AS "Last", 
      employees.email AS "Email", 
      dept.name AS "Dept", 
      managers.name AS "MGR"
    FROM 
      public.dept, 
      public.employees, 
      public.managers
    WHERE 
      employees.dept = dept.id AND
      employees.manager = managers.id
    ORDER BY id;
    Now the following command when ran in my DBMS gives me exactly what I'm looking for:

    Code:
     id | First  |  Last   |      Email       |         Dept         |      MGR       
    ----+--------+---------+------------------+----------------------+----------------
      1 | Carlos | Mennens | cmennens@zoo.com | Storage Management   | Phill Collins
      2 | Ryan   | Smith   | rsmith@zoo.com   | Software Development | Johnny Bananas
      3 | Roger  | Dodd    | rdodd@zoo.com    | Customer Service     | Brad Pickle
      4 | David  | Smith   | dsmith@zoo.com   | Software Development | Johnny Bananas
      5 | Smith  | Fields  | sfields@zoo.com  | Technical Support    | Theo Price
    So my question is why did the query builder not suggest any JOIN statements as you noted before? I realize there are several ways to obtain the same data using ANSI SQL however the above results I wanted above literally have no requirement for JOIN which I assume is just INNER JOIN (most commonly used JOIN keyword).
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    I drew up an Entity Relationship Model for this relation. The image is in the spoiler tags below.



    Converting the ER model into the Relational model we get the following:
    Employee (eID (primary), fName, lName, email, salary, hireDate)
    Supervision(mID, eID (primary))
    WorksFor(deptID, eID (primary))
    Manages(eID (unique),deptID (primary))
    Department(deptID (primary), deptName)

    Merging the relations with the same primary key, we get the following:
    Employee (eID (primary), fName, lName, email, salary, hireDate, deptID)
    Supervision(mID,eID (primary))
    Department(deptID (primary), eID (unique), deptName)

    I reccomend storing the deptName as a String. That way you won't have to worry about determining what the name is based on the integer.

    To get the Manager name and department name to display. The following SQL code should work. I am using the relations above instead of the ones you provided.

    This code will display the department name, first name and last name of all the managers. It does this by performing a natural join based on the eID of the Department and Employee table. This works because the department table only contains the eID of managers.
    Code:
    SELECT deptName,fName,lName
    FROM Department D, Employee E
    WHERE D.eID = E.eID
    Cheers, Cam.

    Comments on this post

    • CarlosinFL agrees : Thanks for drawing the ER model and explaining everything
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by CarlosinFL
    So my question is why did the query builder not suggest any JOIN statements as you noted before?
    Which query builder?
    But in general query builders create mediocre (if not crappy) SQL. If you want to learn SQL, write the statements yourself.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    103
    Rep Power
    3
    Originally Posted by shammat
    Which query builder?
    But in general query builders create mediocre (if not crappy) SQL. If you want to learn SQL, write the statements yourself.
    http://w3schools.com/sql/default.asp is a good ressource for learning how to write SQL statements.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by Cameron0960
    http://w3schools.com/sql/default.asp is a good ressource for learning how to write SQL statements.
    W3Schools is not known for their good quality though...
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by shammat
    W3Schools is not known for their good quality though...
    i agree

    they post only the most basic sql statements, and there is a paucity of examples
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3
    Originally Posted by shammat
    Which query builder?
    But in general query builders create mediocre (if not crappy) SQL. If you want to learn SQL, write the statements yourself.
    I was using the default built-in pgAdmin3. I do want to learn SQL so if I don't know something, I look it up or see how the query builder would perform this task. I can't write my own until I understand them. I was under the impression I didn't have the correct understanding of the JOIN statement.

    Thanks all for the info.

IMN logo majestic logo threadwatch logo seochat tools logo