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.

we won't do homework assignments in this site

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

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.

test this one again, i don't think it works

Thank you but it did not work.

so have you checked that yoiu have got data that will meet those criteria?

what was the error message?

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.

could you please go back to post #4 and do that one

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

Thanks

sorry, i am not familiar with that particular error message

can you give some more detail about what happened when you tested it?

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

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

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);

that's nice, but you keep sidestepping my question

please qualify the columns in the query in post #4