CODEWITHSUNDEEP
sqlpostgresql
banbar
banbar

Reputation: 299

Elmasri Employee Database - Determining those working at different locations

I am working on the Elmasri Employee DB. Please refer to this document document regarding its details. I have obtained the employees working on two or more projects and their respective locations:

select w."Essn", p."Plocation"
from "Works_On" w, "Project" p, "Employee" e
where p."Pnumber" = w."Pno" and w."Essn" = e."Ssn"
        and 
        w."Essn" in 
        (select w1."Essn"
        from "Works_On" w1
        group by w1."Essn"
        having count(*) >= 2
        )

The resulting relation looks like this.

I need to determine those employees that work on two or more different locations. Therefore, 987987987 and 999887777 are not eligible as they are working on two different projects but at the same location. The solution I came up is to group the employees based on their Essn and Plocation attributes and determine those that are equal to one. Updated code would be: 

select base."Essn", base."Plocation"
from
    (select w."Essn", p."Plocation"
    from "Works_On" w, "Project" p, "Employee" e
    where p."Pnumber" = w."Pno" and w."Essn" = e."Ssn"
        and 
        w."Essn" in 
        (select w1."Essn"
        from "Works_On" w1
        group by w1."Essn"
        having count(*) >= 2
        )
    ) as base 

group by base."Essn", base."Plocation"
having count(*) =1

However, this obviously is not true as it would eliminate 33344555 - Stafford.

My question is: would it possible from the given base relation to determine those employees that work on more than x different locations?

Upvotes: 0

Views: 100

Answers (1)

Radim Bača
Radim Bača

Reputation: 10711

In order to find employees that work on two different projects on two different locations you probably just need this query

  select w."Essn"
  from "Works_On" w
  join "Project" p on p."Pnumber" = w."Pno"
  group by w."Essn"
  having count(distinct w."Pno") > 1 and count(distinct p."Plocation") > 1

and having query like this should be sufficient as well

  select w."Essn"
  from "Works_On" w
  join "Project" p on p."Pnumber" = w."Pno"
  group by w."Essn"
  having count(distinct p."Plocation") > 1

Upvotes: 1

Related Questions