agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: select where not in () fails
Date: Fri, 21 Sep 2018 16:08:41 +0100
Message-ID: <201809211608.41086.gary.stainburn@ringways.co.uk> (raw)
I have a users table with u_id as primary key.
I have an employee record table with emp_u_id is a foreign key back to the
users table.
A user may have zero or more employee records (leaves then returns / changes
department).
An employee may have zero or one user record
The select I am trying to get working to so be able to list all users without
an employee record. Straight forward right?????
Can anyone see why user record 2212 doesn't appear in the last select
statement?
users=# select count(u_id) from users;
count
-------
716
(1 row)
users=# select count(emp_u_id) from employees;
count
-------
345
(1 row)
users=# select count(*) from employees;
count
-------
388
(1 row)
users=# select emp_u_id from employees where emp_u_id=2212;
emp_u_id
----------
(0 rows)
users=# select u_id from users where u_id=2212;
u_id
------
2212
(1 row)
users=# select count(u_id) from users where u_id in (select distinct emp_u_id
from employees);
count
-------
323
(1 row)
users=# select count(u_id) from users where u_id not in (select distinct
emp_u_id from employees);
count
-------
0
(1 row)
view thread (5+ messages) latest in thread
Message-ID: <201809211608.41086.gary.stainburn@ringways.co.uk>
Permalink: ../201809211608.41086.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201809211608.41086.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
Subject: Re: select where not in () fails
In-Reply-To: <201809211608.41086.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox