CODEWITHSUNDEEP
mysqlsql
Sam Kohnson
Sam Kohnson

Reputation: 115

SQl query Multiple select statments in the same table under different conditions

So I have 3 tables that I want to perform some queries on but during that process I end up returning back to a table I already performed some functions on and it.

I first get the essn from one dependent table and search the employee table for a ssn matching it then i got the superssn and compare to the mgrssn in another table. The last step is to go back into the employee table and find the name of the person who has the same ssn of mgrssn.

The issue here is that once i get the matching superssn I can't access the other rows.

select lname, fname from
(select mgrssn from department) as d,
(select superssn, lname,fname,ssn from
    (select essn from dependent where dependent_name ='joy') as de,
    (select ssn,lname,fname,superssn from employee) as e
where essn =ssn) as s
where s.ssn = mgrssn

Should I look into doing joins instead?

Upvotes: 0

Views: 267

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You don't need the subqueries. Your query is a bit hard to follow (you don't have table aliases for all the columns), but I think this is what you are trying to do:

select lname, fname
from department d join
     employee e
     on e.ssn = d.mgrssn join
     dependent dep
     on dep.essn = e.ssn
where dep.dependent_name ='joy';

Simple rule: Never use commas in the from clause. Always use explicit join syntax.

Upvotes: 1

Related Questions