0

I'm trying to JOIN two columns from ps_products and one from ps_product_lang into one table in the PrestaShop database with a query, but I can't figure it out.. Whenever I run the code below, I just get NULL values for name, and vice versa when I switch RIGHT and LEFT. I've tried using UNION ALL, putting parenthesis around the SELECT queries and using WHERE, but nothing works.. The database runs MySQL, so FULL OUTER JOIN isn't possible without a workaround. I'm not very experienced in SQL, so I might've missed something here.

The code:

SELECT 
ps_product_lang.name AS 'name', 
ps_product.id_product AS 'product id', 
ps_product.reference AS 'reference'
FROM ps_product_lang
RIGHT JOIN ps_product ON name = reference
UNION
SELECT
ps_product_lang.name AS 'name', 
ps_product.id_product AS 'product id', 
ps_product.reference AS 'reference'
FROM ps_product_lang
LEFT JOIN ps_product ON name = ps_product.reference

Any idea? Thanks!

  • 1
    You have typos on "refrence" - and should you be using quotes for your AS's...? – Stuart Jun 27 '17 at 08:48
  • 1
    If name is NULL in the right join, then it means `name = reference` did not find a match. – Adder Jun 27 '17 at 08:54
  • 1
    Fixed the typos, thanks! I added the aliases for better readability in the post, same goes for the quotes(which aren't required), but it doesn't make any difference wherever I use them or not in the query though. @Adder Any idea how to fix that? A bit unsure how I should write my WHERE statement. –  Jun 27 '17 at 08:58
  • Either fix the data in the database, or use a single select with an INNER JOIN. The way you are doing it you are bound to get names that are NULL and also from the second select product ids that are NULL. If that is not what you want, make the change. – Adder Jun 27 '17 at 09:06
  • It's that single quotes are for string literals. For alias names one should use double quotes. MySQL also allows non-standard backticks for alias names. You haven't put a qualifier for `name` by the way. Maybe this is already your issue? Do both tables have a `name` column maybe? Then why do you expect to find a `ps_product_lang` without a matching `ps_product`? Isn't there a foreign key constraint on the tables? There should be! – Thorsten Kettner Jun 27 '17 at 09:06
  • Look at the graphics on the answer of this: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join?rq=1 – Adder Jun 27 '17 at 11:05

1 Answers1

1

You cannot join tables based on product reference and its name. Product reference is SKU and product name is just a name depending on the language. You need to join tables with id_product.

SELECT ps_product.id_product, ps_product.reference, ps_product_lang.name
FROM ps_product
LEFT JOIN ps_product_lang ON (ps_product.id_product = ps_product_lang.id_product)
WHERE ps_product_lang.id_lang = language_id AND ps_product_lang.id_shop = shop_id

Replace language_id and shop_id with proper values.

You're seriously lacking the knowledge of how relational databases work so you should read some tutorials.

TheDrot
  • 4,246
  • 1
  • 16
  • 26