Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    sql query with left outer join

    Hello, can anyone help me with this sql query please?
    Iam trying to "retrieve the names of department managers who have no dependent " using ISNULL (instead of exists, not exists, in...)
    Basically, i have the Employee table in which ssn is the primary key and the foreign key (dno) references the Department table. The dependent table is linked to Employee table by essn= ssn.

    Any help is greatly appreciated.
    Last edited by rikki27401; 08-03-11 at 22:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we won't do homework assignments in this site

    however, we will help, if you show what you've tried

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    7

    sql query with left outer join

    Thank you for your desire to help. I tried several statements but none seems to work.
    Here what i got:
    first i tried to retrieve the names department managers like this:
    select name
    from employee, department
    where ssn=mgrssn;
    That gives me the list of department mangers.
    Next i tried to retrieve the employees with dependents so i can see which employee does not have a dependent
    select essn
    from dependent
    where essn=ssn
    not with those two lists , i can see the manager that does not have a dependent .But how to join the two lists to show the desired result is where iam stuck
    I tried doing this and it is not working:
    select name
    from employee,department
    where ssn=mgrssn
    and(select essn
    from dependent
    where essn=ssn and mgrssn is null);
    I also tried the left outer join but it would not work either.
    Thanks for any guidance.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rikki27401 View Post
    select essn
    from dependent
    where essn=ssn
    test this one again, i don't think it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    7

    sql query with left outer join

    Thank you but it did not work.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so have you checked that yoiu have got data that will meet those criteria?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rikki27401 View Post
    Thank you but it did not work.
    what was the error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2011
    Posts
    7

    sql left outer join

    yes i checked and there is one manager that does not have dependents .
    I just cant seem to find a way to the name of that manger from the dependent table and employee table

    For example, for these two attempts this is the error iam getting:
    SQL> select name
    2 from employee,department
    3 where ssn=mgrssn
    4 and (select essn
    5 from dependent
    6 where essn=ssn)
    7 and mgrssn is null;
    and mgrssn is null
    *
    ERROR at line 7:
    ORA-00936: missing expression


    SQL> select name
    2 from employee,department
    3 where ssn=mgrssn (select essn
    4 from dependent
    5 where essn is null);

    *
    ERROR at line 3:
    ORA-00936: missing expression


    Thank you.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please go back to post #4 and do that one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2011
    Posts
    7

    sqlquery left outer join

    I went back to post#4, still not working, not sure why.

    Thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rikki27401 View Post
    still not working,
    sorry, i am not familiar with that particular error message

    can you give some more detail about what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2011
    Posts
    7

    sql left outer join

    Thank you.
    basically iam trying to acchive the same result as below only without using"not in, not exists in but rather with IS NULL and maybe an outer join)
    select name
    from employee,department
    where ssn=mgrssn
    and mgrssn not in(select essn
    from dependent);

    This one gives me the result i want. but now i need to get the say result ,this time using "IS NULL " or an other join.

    Thanks for your efforts

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you understand how to qualify a column name with its table name?

    because you are showing us queries that are impossible for us to understand, since you aren't qualifying your columns properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2011
    Posts
    7
    Ok, sorry.
    select employee.name
    2 from employee,department
    3 where employee.ssn=department.mgrssn (select dependent.essn
    4 from dependent
    5 where depedent.essn is null);

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's nice, but you keep sidestepping my question

    please qualify the columns in the query in post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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