CODEWITHSUNDEEP
sqlpostgresql
AntonAL
AntonAL

Reputation: 17410

SELECT with JOIN and condition and without JOIN in one query

In PostgreSQL I have two tables:

company id name

owner company_id verified

There is no owner records, presented in DB without associated company.

But we have company records, presented in DB without owner.

How to select all companies with only verified owners and companies without owners with one query?

I've tried many queries and no one is working :(

For example, this query is not working:

select count(c.id) 
from company as c 
  left outer join owner o on c.id = o.company_id and o.verified is not null 
where not (c.id = o.company_id and o.verified is null);

Sample schema

http://sqlfiddle.com/#!17/ab366

create table company (
  id int unique,
  name varchar(255)
);

create table owner (
  first_name varchar(255),
  company_id int unique references company(id) on update cascade on delete set null,
  verified boolean
);

insert into company values (1, 'company1');
insert into company values (2, 'company2');
insert into company values (3, 'company3');

insert into owner values ('owner1', 1, true);
insert into owner values ('owner2', 2, false);

I need to select company1 and company3.

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I would use filtering in the where clause:

select c.*
from companies c
where not exists (select 1
                  from owners o
                  where o.company_id = c.id and
                        not o.verified
                 );

The primary reason for this is that a left join version can return duplicates if there are multiple verified owners.

A secondary reason is that this more closely captures the logic that you are describing . . . you want companies that have no unverified owners.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

I would actually use a left join from the company table to the owner table here:

SELECT c.*
FROM company c
LEFT JOIN owner o
    ON c.id = o.company_id
WHERE
    o.company_id IS NULL OR    -- companies without owners
    o.verified IS NOT NULL;    -- companies with verified owners

Upvotes: 4

Related Questions