CODEWITHSUNDEEP
phpmysqlsqlopencart
Lizard Derad
Lizard Derad

Reputation: 379

How to make left join on specific rows in right table?

My website may have individual price list for user. When I left join imported product table on all products table I don't know where to put WHERE which would only do left join on specific rows at right table (logged user rows).

I need to do this in opencart, but it doesnt matter, it's just SQL

$sql = "SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON 
(p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "import i ON (p.ean = i.ean_code) WHERE 
i.import_id = '" . (int)$query->row['import_id'] . "'";

I somehow need to do WHERE on import table first, but in example above it does it afterwards. Do I have to execute 2 queries?

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use parameters where you can. You need to move the condition to the on clause:

SELECT *
FROM " . DB_PREFIX . "product p LEFT JOIN
     " . DB_PREFIX . "product_description pd
     ON p.product_id = pd.product_id LEFT JOIN
     " . DB_PREFIX . "import i
     ON p.ean = i.ean_code AND 
        i.import_id = ?

You should also be selecting the explicit column names. This is particularly important in a LEFT JOIN, because the tables share column names (at least product_id) and you don't know which is referenced by "product_id" in the application code.

Upvotes: 1

Related Questions