Use id values from one query, to corresponding column with same id in another table

Question

This question is a bit vague and I do apologize, hopefully the example below will clear it up. It's a fairly elementary question, I just can't seem to quite find the right solution with my very limited knowledge and SQL relevant vocabulary

There is a table with people,

create table People (
id          integer, 
name        LongName,
primary key (id)
);

And one for workers that references people

create table Workers (
id          integer references People(id),
worktype    varchar(20),
primary key (id)
);

and lastly a works_for relationship

create table Works_for (
worker      integer references Workers(id),
employer    integer references Job(id),
primary key (worker,job)
);

Now what I want to do is get all people that work at least 20 jobs, so I get the correct list of id's with the following query:

SELECT worker
FROM Works_for
GROUP BY worker 
HAVING COUNT(worker) > 20;

However I also want to get the names of these workers. How would I go about this? I've tried a number of things but I keep running into errors. Any help would be much appreciated!

Answer 1

You can join the tables and select both fields like this:

SELECT p.name, p.id
FROM People p
JOIN Works_for wf ON (p.id = wf.worker)
GROUP BY id 
HAVING COUNT(wf.worker) > 20;

sqlfiddle

Answer 2

SELECT worker,name
FROM Works_for join People on worker=id
GROUP BY worker,name 
HAVING COUNT(employer) > 20;

http://sqlfiddle.com/#!15/e03e3/1 There will be no 20 but just 3 records but I think it's enough as a demo

Answer 3

you can use left join:

select worker, name, worktype from
(select worker,MIN(employer) as employer 
from works_for group by worker having COUNT(worker)>20) w
left join People p on p.id = w.employer
left join Workers ws on  ws.id = w.worker

like this, you also get people name and work type

Answer 4

Query

While working with your current schema:

SELECT id, p.name
FROM  (
   SELECT worker AS id
   FROM   works_for
   GROUP  BY 1 
   HAVING count(*) > 20
   ) wf
JOIN   people  p USING (id);

It's faster to aggregate and eliminate irrelevant rows first and then join. Test with EXPLAIN ANALYZE. count(*) is also a bit faster than count(worker). It does the same as long as worker cannot be NULL, which is the case here.

There are many related answers:

• GROUP or DISTINCT after JOIN returns duplicates

Schema

Your presented table layout is odd. The workers table is a 1:1 extension to people. You might as well add the column worktype to people and drop the table workers. But it's a surprising limitation that a person can only have a single worktype ...

Typically, it would look something like this:

CREATE TABLE person (
  person_id serial PRIMARY KEY
, name      text NOT NULL  -- custom domain?
);

CREATE TABLE job (
  job_id serial PRIMARY KEY
, name   text NOT NULL
-- more
);

CREATE TABLE person_job (
  person_id   int REFERENCES person
, job_id      int REFERENCES job
, worktype_id int REFERENCES worktype
, PRIMARY KEY (person_id, job_id)
);

The type of work goes into person_job or the job table. Etc. More:

• How to implement a many-to-many relationship in PostgreSQL?