CODEWITHSUNDEEP
sqlpostgresql
Qoutroy
Qoutroy

Reputation: 113

Is there a way to select all from one table, and join if possible?

I'm quite a beginner when it comes to SQL and postgreSQL, so I don't know if this is an obvious question. I have two tables: products and images. I need to somehow select all products, and if it exists, their thumbnail from the images table.

This is what I have so far:

CREATE TABLE products (
  ID SERIAL PRIMARY KEY,
  ....
);

CREATE TABLE product_files (
  ID SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(ID),
  is_thumbnail BOOLEAN NOT NULL DEFAULT FALSE,
  ...
);


SELECT p.id as product_id, p.status, p.title, p.description, pf.id, pf.file_name, pf.is_thumbnail
FROM products p LEFT JOIN product_files pf
ON p.id=pf.product_id
WHERE pf.is_thumbnail=true;

But i can't get it to select all of the products, even the ones without an image with thumbnail=true.

Upvotes: 1

Views: 323

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The WHERE clause is turning the outer join to an inner join, because non-matches are filtered out.

Just move the condition to the ON clause:

SELECT p.id as product_id, p.status, p.title, p.description, pf.id, pf.file_name, pf.is_thumbnail
FROM products p LEFT JOIN
     product_files pf
     ON p.id  =pf.product_id AND pf.is_thumbnail;

There is nothing wrong with having = true. However, is_thumbnail is a boolean, so there is no reason for an explicit comparison either.

The general rule when filtering for LEFT JOINs is to put the conditions in the first table in a WHERE clause. And filtering conditions on subsequent tables in the ON clause.

Upvotes: 3

user330315
user330315

Reputation:

You need to move the condition for the thumbnail out off the WHERE into the JOIN condition:

select ..
FROM products p 
  LEFT  JOIN product_files pf ON p.id=pf.product_id AND pf.is_thumbnail=true;

Upvotes: 1

Related Questions