CODEWITHSUNDEEP
sqlsql-serverdatabase
natsucule
natsucule

Reputation: 19

SQL - Why Does This Happen?

These are the tables that I'm working with.
With that in mind, I want to showcase the Employees that are both a supervisor and a manager.

But when I used this

select e1.fname,e1.lname
from employee e1,employee e2,department
where e1.ssn=e2.super_ssn and e1.ssn = Mgr_ssn

This was the output
I know I can solve the problem with 'distinct', but I'm more interested to know why the output turned out like it did.

Upvotes: 1

Views: 64

Answers (2)

rathna
rathna

Reputation: 1083

add department matching clause in where like

 select e1.fname,e1.lname
 from employee e1,employee e2,department d
 where e1.ssn=e2.super_ssn and e1.ssn = Mgr_ssn and 
 d.Dnumber=e1.Dno

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

How about exists?

select e.*
from employee e
where exists (select 1 from employee e2 where e2.mgr_ssn = e.ssn) and
      exists (select 1 from employee e2 where e2.super_ssn = e.ssn) ;

Your query returns duplicates for two reasons. First, presumably managers and supervisors have multiple employees below them. You end up with rows for each such employee. Second, you have a cartesian product with department, which further multiplies the rows. The department table is not used in the query.

Using select distinct is not a good solution in this case. The database just ends up having to do a lot more work than necessary -- first to create the duplicate rows and then to remove them.

Upvotes: 1

Related Questions