I am working with a query in the prestashop database and I am not really an expert with sql queries, at the moment it works fine for me but I also need to include those that are not inner joins. To be more specific I want to obtain products that have combinations and this is where the join comes in, but I also need to extract the products that do not have combinations, in this part is where I have my doubts, in also including the products of the tableps_product
SELECT
prod.id_product, prodla.name, prodat.reference,
SUBSTRING(prod.price, 1, LENGTH(prod.price)-7) as precio,
SUBSTRING(prodat.price, 1, LENGTH(prodat.price)-7) as precio_combinado,
(prod.price + prodat.price) as diferencia,
CEILING(((prod.price + prodat.price)*1.19)) as precio_con_iva
FROM ps_product as prod
JOIN ps_product_attribute as prodat
JOIN ps_product_lang as prodla
WHERE prod.id_product = prodat.id_product AND prod.id_product = prodla.id_product
ORDER BY prod.id_product ASC
for example I have 10 products, of these 5 have combinations, which as a total would give me 15 results in my query, based on the fact that I also have 1 combination for each combined product
I was thinking of a select that brings everything from ps_product and a select inside the main select that brings the combinations, I don't know if it's possible to do it this way.
Well I have already solved it between investigating and doing some tests, use LEFT JOIN and RIGHT JOIN when working with 3 tables in total