Having problems performing the opposite in MySQL

I need to perform the complete opposite of this Query

[ineedsomex12s]

I need to perform the complete opposite of the below query

select Distinct lname from (employee, dependent,department)
where essn= mgrssn and ssn=essn and mgrssn=ssn;

I have tried using not exists and not in, but it still does not return the correct data. The above query shows me the two managers that has an entry in then dependent table, there are only 3 managers I am trying to get it to display the manager that does not have a relation to the dependent table. By the way I have already tried putting!= for the ssn,essn and mgrssn and that doesnt work either, but basically I need to display the lname of the mgr who does not show when the above query is run.Please Help!

[Demaestro]

If I understand you correctly then you want.....

select Distinct
lname
from
employee
where
essn not in (select mgrssn from table_that_has_mgrssn_value)
and ssn not in (select essn from table_that_has_essn_value)
and mgrssn not in (select ssn from essn from table_that_has_ssn_value);

[ineedsomex12s]

Ok thanks for your help I am very new and am still learning so please be patient with me. based on what you gave me my query looked like this...
select Distinct lname
from employee
where essn not in (select mgrssn from department)
and ssn not in (select essn from dependent)
and mgrssn not in (select ssn from essn from dependent);

It did not work! Probably because of the way I typed it, but basically the way the table looks is below.....
employee table
lname ssn
joe 12
john 34
jim 56

department
dname mgrssn
ab 12
cd 34
ef 56

dependent
essn depName
12 Julie
34 Josh
89 Jim
10 John

I need to only display the lname of the person in the employee table that does not have a dependent but is a manager or has a mgrssn basically.

[Demaestro]

We are close then my friend......

select Distinct lname
from employee
where essn not in (select mgrssn from department)
and ssn not in (select essn from dependent)
and mgrssn not in (select ssn from essn from dependent);

what is missing from this is where it looks to see if they are a manager.

I am thinking that this line:

where essn not in (select mgrssn from department)

needs to be removed and instead needs logic to see if they are a manager.. I don't know enough about your tables but something like this.....

where essn = "manager"

Just try to look at the logic of what is being said in the where clause and try to fit it to make sense to what you want returned.... I will keep helping... keep posting.

[ineedsomex12s]

Ok here is what I put
select Distinct
lname
from
employee,dependent,department
where
ssn in (select mgrssn from department)
and mgrssn not in (select essn from dependent);

now this query displays the 3managers and I do not understand why? To me based on the last line mgrssn not in (select essn from dependent) should exclude the other 2managers. I have been at this for about a day and a half and it is probably something simple that I will kick myself for. But I see it as this [where ssn in(select mgrssn from department) checks for the manager, then [and mgrssn not in (select essn from dependent); should exclude the 2managers that have essn numbers in the dependent table, but it doesnt it displays all three managers even though 2 of them have essn numbers. What am I missing in this. By the way thanks in advance!

[Demaestro]

Are ssn and mgrssn fields on the employee table?

[ineedsomex12s]

The SSN is in the employee table
The mgrssn is in the department table and
The essn is in dependent table much similar to the below table
employee table
lname ssn
joe ... 12
john... 34
jim ... 56

department
dname mgrssn
ab ... 12
cd ... 34
ef ... 56

dependent
essn depName
12 ... Julie
34 ... Josh
89.... Jim
10 John
so when I run my qeury it should only show Jim for lname if I am running it right, because jim is the only one that has a mgrssn and is not in the dependent table, but when I run it, it shows joe,john and jim

[Demaestro]

Try:

select Distinct
lname
from
employee
where
ssn in (select mgrssn from department)
and ssn not in (select essn from dependent);

[ineedsomex12s]

Thank you so much! That had to have been the only way I did not try it. Why did the other way still include the others, I still do not understand that, but the change you made definitely worked and I do understand the logic behind it but it seems that It should have displayed the same with the other query as well, but thats just my novice opinion as I am still learning and am pretty much a student and you guys in this forum are awesome and I hope one day to be able to pay forward the knowledge.

Thanks again

[Demaestro]

i think the reason was 2 fold.....

1 you were referencing the tables (employee,dependent,department) in the from clause but you weren't using them all... for example in this query

select Distinct
lname
from
employee,dependent,department
where
ssn in (select mgrssn from department)
and mgrssn not in (select essn from dependent);

You aren't referencing anything from the dependent table until in the sub-select which then uses it but it only need be defined in the sub-select not the top level select... this will slow the query down and may cause it to do a Cartesian join which is bad.

Second you didn't add logic to tell it that the row from the employee table should match the row from the department table...

I think this would work as well.. but is slower.

select Distinct
lname
from
employee,department
where
ssn in (select mgrssn from department)
and mgrssn not in (select essn from dependent)
and ssn = mgrssn;

We have to join the 2 tables in the from clause. To do that we have to make sure the ssn from the employee table is tied to the mgrssn of the department table.... that can be done like this... and ssn = mgrssn

No join is needed the way it is working now because there is only one table in the top level "from" clause.... which is why I believe it will run faster... but sub-selects can slow things down depending on DB versions and types and that sort of thing... the difference will not be noticeable unless in extreme load times.

Make sense?

Also... you are welcome... I was were you are and I am now paying it forward.... Karma baby it goes around... I still get my hand held through some CSS stuff on here by others.

[edited by: Demaestro at 7:24 pm (utc) on Sep. 28, 2007]

[ineedsomex12s]

OK, I perfectly understand now. You are awesome, I hope to one day hold that type of knowledge toward this, this is my first semester in Grad school and I am working on training my brain to use logic and definitely not to overthink things, but your explination seeps in well and makes perfectly good sense based on your second query that would probably be slower shows that I was almost there though, and I hope to get better at writing efficient queries with more practice.

Thanks for all your help

from an inspiring to be DBA

[Demaestro]

Just because you are in school and you might be getting a grade on your schema you might want to just call that field ssn in every table then when doing SQL just alias the tables

So your original query would look like this

select Distinct
empl.lname
from
(employee as empl, dependent as depn,department as depr)
where
depn.ssn = depr.ssn
and empl.ssn = depn.ssn
and depr.ssn= empl.ssn;

Having them all named the same makes it easier to read...IMO... because you can see what table is being joined to what table based on the alias... where as in yours you have to have intimate knowlage about the structure to know to join on those fields.... it really shows someone who doesn't know the table schema that those fields can be joined on each other... more of a "good coding practice" then anything... I will say if it had been structured that way to start it would have been more obvious to me what the solution was.

This is just to get you those extra couple % marks....if you teacher even looks for that... mine did.

[edited by: Demaestro at 8:58 pm (utc) on Sep. 28, 2007]

[ineedsomex12s]

Yes, I agree. That would have made it easier to identify and make the query. Unfortunately though, that was the table the professor gave, for us to make the query from, if I created it I definitely would have done it differently.

But thanks for all your help and advice it will be used in the future.