LEFT JOIN query in Postgres

Question

I'm trying to make select query in Postgres using LEFT JOIN but I can't get proper results. I have 2 tables - offers and images and when offers have images, they are inserted in table images. But not all offers have images.

Now, I want to select all offers (both with images and without images) from what is searched and if these offers have images, to show them. I tried using LEFT JOIN to select from images if there are images, but when I'm trying to display them, there are NULL values - without any returned values.

What's wrong with my query?

$sql->query("SELECT count(offers.id) as offers_count FROM offers LEFT JOIN images ON images.offer_id = offers.id AND images.file_type = 3 WHERE $where"); //$where is what is searched from user


$SQL = "SELECT  images.filename, * FROM offers LEFT JOIN images ON images.offer_id = offers.id AND images.file_type = 3 WHERE  $where $result LIMIT 12 OFFSET ".($page-1)*12);

for ($i=0; $i<$sql->getNumRows(); $i++) {
$obj = $sql->getRow($i);
offers::show_offer($obj); 
}

function show_offer($offer_obj) {
$img = $offer_obj['filename']; 
$picture = '<img src="/'.$img.'"  />';
echo '<div>';
echo $picture; 
//some other info I show
echo '</div>';

}

I ran it into Postgre Client, but I can't find where problem is.When there is no image, it is NULL, but instead not showing this image, whole info for this offer is not shown and even no id. — ivva
– ivva, Commented Dec 28, 2015 at 20:16
@a_horse_with_no_name: select table1.col1, * works in Postgres. I believe it is an error Oracle, did you mean that? — Andomar
– Andomar, Commented Dec 28, 2015 at 20:17
If you don't get an error, but an empty result you need to show us the real query you are executing, not the PHP(?) code. If you don't get an empty result but a wrong one, edit your question add some sample data (ideally as insert into) statements and the result you get (as formatted text) including the real statement that is executed. — user330315
– user330315, Commented Dec 28, 2015 at 20:19

Daniel Vérité · Accepted Answer · 2015-12-29 21:32:20Z

That can't work because of this condition in the WHERE clause:

images.file_type = 3

For rows that don't match at the right side of the LEFT JOIN, the SQL engine will put NULL into all columns corresponding to the right side table. Then, the condition images.file_type = 3 is evaluated to false, as images.file_type is NULL. So the rows without image are eliminated.

In effect, this condition cancels the effect of the LEFT JOIN, making it the equivalent of an INNER JOIN (the same as JOIN alone)

You may solve this by applying the restriction on file_type before the LEFT JOIN, like this:

SELECT i.somecolumn, offers.* FROM
    offers
 LEFT JOIN
   (select * from images WHERE file_type=3) AS i
 ON i.offer_id = offers.id 
 WHERE  $where ...

Make sure that the other clauses in $where are also not conditions ON images.* columns, or they must be processed similarly.

Collectives™ on Stack Overflow

LEFT JOIN query in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related