CODEWITHSUNDEEP
sqlselectdatabase
Abdullah Sorathia
Abdullah Sorathia

Reputation: 91

How does select query works?

I having trouble with this query it is executing quit well but I cannot make out how is this select statement working. Any help or explanation on this problem will be appreciated .. thank you these are my tables and query here am looking for the employee who lives in same city as the the company for which they work

Table:-emp

eid         name             street        city
----------- ---------------- ------------- ------------
1           yeman            asd           vasai
2           aksh             adssd         mumbai
3           chintan          ghfgh         mumbai
4           samual           ghfdgh        bandra
5           ddlj             fghfgh        andheri
6           jack             fghnfg        Bandra
7           bridge           gfhfgh        vasai
8           rahim            ghfgh         mumbai
9           chirag           fghfghfg      bandra
10          mistry           hhhty         bandra
11          ravi             tytey         andheri

Table:- company

cid         companyname            city
----------- -------------------    ------------
1           Vasai Industries       vasai
2           Mumbai Pharmacy        mumbai
3           bandra loft            bandra
4           andheri tactics        andheri

Table:= works

eid         cid         salary
----------- ----------- -----------
1           1           200
2           3           4831
3           4           4457
4           2           20001
5           1           32221
6           2           224
7           3           784
8           1           336
9           3           2489
10          2           4789
11          1           22541

Query

  select * from emp
    where eid 
    IN (select eid from works 
         where cid=(select cid from company 
                    where city=emp.city))

Upvotes: 1

Views: 758

Answers (2)

echo_Me
echo_Me

Reputation: 37233

why not use this query with joins and its easy to understand then a bunch of subqueries.

 select * from emp
 inner join works    on works.eid = emp.eid
 inner join company  on company.city=emp.city

Upvotes: 3

Ajay
Ajay

Reputation: 2080

Explanation:

1.select cid from company where city=emp.city

Here you are getting city id regarding cities which are same in emp and company

2.

select eid from works 
     where cid=(select cid from company 
                where city=emp.city)

Here you getting collection of id's from works table which cid is same in emp and company

3.

select * from emp
where eid 
IN (select eid from works 
     where cid=(select cid from company 
                where city=emp.city))

here you are getting all records based on emp id's whose cities are same in emp and city

Upvotes: 1

Related Questions