CODEWITHSUNDEEP
sqlpostgresqlselectjoin
ivva
ivva

Reputation: 2959

LEFT JOIN query in Postgres

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>';

}

Upvotes: 1

Views: 6792

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61686

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.

Upvotes: 1

Related Questions