CODEWITHSUNDEEP
sqlpostgresql
mhyder1
mhyder1

Reputation: 107

Postgres query all results from one table blended with conditional data from another table

I have 2 SQL tables and I'm trying to generate a new table with data from the 2 tables.
Jobs table:

jobs (
  id SERIAL PRIMARY KEY,
  position TEXT NOT NULL,
  location TEXT NOT NULL,
  pay NUMERIC NOT NULL,
  duration TEXT NOT NULL,
  description TEXT NOT NULL,
  term TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
)

Applied table:

applied (
  id SERIAL PRIMARY KEY,
  completed BOOLEAN DEFAULT FALSE,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  job_id INTEGER REFERENCES jobs(id) ON DELETE SET NULL,
  UNIQUE (user_id, job_id)
);

The tabled populated with data look like this:
Jobs table enter image description here

Applied table enter image description here

I want my final query to be a table that matches the jobs table but that has a new column called js_id with true or false based on whether the user has applied to that job. I want the table to look like this:

enter image description here

Here is the query I came up with to generate the above table:

SELECT DISTINCT on (jobs.id) 
            jobs.*, applied.user_id as applicant,
           CASE WHEN applied.user_id = 1 THEN TRUE  
                ELSE FALSE END as js_id
        FROM jobs 
        JOIN applied on jobs.id = applied.job_id;

However this doesn't work as I add more applicants to the table. I get different true and false values and I haven't been able to get it working. When I remove DISTINCT on (jobs.id) my true values are consistent but I wind up with a lot more than the 3 jobs I want. Here are the results without the DISTINCT on (jobs.id):

enter image description here

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want exists:

SELECT j.*,
       (exists (select 1
                from applied a
                where a.job_id = j.id and a.user_id = 1
       ) as js_id
FROM jobs j;

Upvotes: 2

Related Questions