Filtering data at the end after doing double LEFT JOIN using this data from LEFT JOIN

Question

Got a tables:

CREATE TABLE users (
    id serial NOT NULL,
    firstname varchar(64) NOT NULL,
    lastname varchar(64) NOT NULL,
    kid integer NOT NULL
);

CREATE TABLE keys (
    id serial NOT NULL,
    key varchar(1024) NOT NULL,
    tid integer NOT NULL
);

CREATE TABLE terminals (
    id serial NOT NULL,
    name varchar(64) NULL,
    ipaddr integer NOT NULL
);

Got a data:

INSERT INTO users (firstname, lastname, kid) VALUES ('jas', 'wedrowniczek', 1);
INSERT INTO users (firstname, lastname, kid) VALUES ('mariusz', 'kolano', 2);
INSERT INTO users (firstname, lastname, kid) VALUES ('ziobro', 'zbigniew', 3);
INSERT INTO users (firstname, lastname, kid) VALUES ('kornel', 'makuszynski', 4);
INSERT INTO users (firstname, lastname, kid) VALUES ('henryk', 'sienkiewicz', 5);

INSERT INTO keys (key, tid) VALUES ('key1', 1);
INSERT INTO keys (key, tid) VALUES ('key2', 2);
INSERT INTO keys (key, tid) VALUES ('key3', 3);
INSERT INTO keys (key, tid) VALUES ('key4', 4);
INSERT INTO keys (key, tid) VALUES ('key4', 5);

INSERT INTO terminals (name, ipaddr) VALUES ('pokoj1', 180879367);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj2', 180879468);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj3', 288704395);
INSERT INTO terminals (name, ipaddr) VALUES (NULL, 288703396);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj5', 0);

I need to get all the logins which have keys to terminal without descriptions or ipaddresses.

The results should be:

 id | firstname |   lastname   |  ipaddr   | name    |
----+-----------+--------------+-----------+----------
  4 | kornel    | makuszynski  | 288703396 |         |
  5 | henryk    | sienkiewicz  | 0         | pokoj5  |

My try:

SELECT u.id, u.firstname, u.lastname, t.ipaddr, t.name
FROM users u
LEFT JOIN keys k ON (u.kid = k.id)
LEFT JOIN terminals t ON (t.id = k.tid)
WHERE
    t.ipaddr = 0
    OR t.name IS NULL;

It gives me empty result.

I understand that: first is going SELECT then filtering is done using WHERE then LEFT JOIN.

...but is it possible to add some filtering at the end using data from LEFT JOIN?

Why do you need a `LEFT JOIN` here? An `INNER JOIN` would produce the result you expect. — GMB, Apr 26 '23 at 16:16
Create a [fiddle](https://www.db-fiddle.com/): make your example reproducible first. — lemon, Apr 26 '23 at 16:16
What does "add some filtering at the end using data from LEFT JOIN" mean? This is not clear. PS [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) — philipxy, Apr 26 '23 at 16:21
If I fix the table definitions so the DDL portional actually runs, then the SQL code produces the expected result. See it here: https://dbfiddle.uk/3M3JlE3i Therefore I must conclude the problem is incorrectly stated and have to vote to close until it's edited to be correct :( Also, based on the problem statement you probably still want INNER JOIN. You can see in the example link they still work as expected. — Joel Coehoorn, Apr 26 '23 at 16:32
`LEFT JOIN terminals t ON t.id = k.tid and (t.ipaddr = 0 OR t.name IS NULL)` — shawnt00, Apr 26 '23 at 16:43
I just did a mistake in column names in sql query on my test envirnoment . Many minutes I was googling. Then found Your answer whitch leads me to right answer. Thanks for Your time and engagement. — Mario Bash, Apr 27 '23 at 13:12

score 1 · Answer 1 · answered Apr 26 '23 at 16:26

When you created terminals you specified NOT NULL and then tried to insert name as NULL. Also, it's better practice to filter data before joining tables.

with t as (
select *
from   terminals 
where  ipaddr = 0 
   or  name is null 
)

select u.id 
      ,u.firstname 
      ,u.lastname   
      ,t.ipaddr   
      ,t.name    
from   users u join keys k using(id) join t on k.tid = t.id

id	firstname	lastname	ipaddr	name
4	kornel	makuszynski	288703396	null
5	henryk	sienkiewicz	0	pokoj5

Fiddle

Filtering data at the end after doing double LEFT JOIN using this data from LEFT JOIN

1 Answers1