I have two SQL functions that I want to integrate in able to make a prestashop export with a single SQL query:
Function 1 joins data from different tables.
Function 2 converts multiple rows into a single row.
I am unable to have these functions work together... Let me describe the two functions.
FUNCTION 1
SELECT a.id_product, a.ean13, a.weight, b.id_product, b.name, c.id_product, c.id_tab, c.content
FROM ps_product AS a
INNER JOIN ps_product_lang AS b ON b.id_product = a.id_product
INNER JOIN ps_extraproducttab_product_lang AS c ON c.id_product = a.id_product
These INNER JOINS work fine:
+------------+---------------+-------------+-----------+--------+-------------------+
| id_product | ean13 | weight | name | id_tab | content |
+------------+---------------+-------------+-----------+--------+-------------------+
| 11 | 0000000000001 | 1000.000000 | product_A | 1 | some ingredients |
| 11 | 0000000000001 | 1000.000000 | product_A | 2 | some allergenes |
| 12 | 0000000000002 | 1500.000000 | product_B | 1 | other ingredients |
| 12 | 0000000000002 | 1500.000000 | product_B | 2 | other allergenes |
+------------+---------------+-------------+-----------+--------+-------------------+
But I want to convert c somehow. The second INNER JOIN uses a table that has multiple rows on a single key (id_product):
+--------+------------+---------+-------------------+
| id_Tab | id_product | id_lang | content |
+--------+------------+---------+-------------------+
| 1 | 11 | 1 | some ingredients |
| 2 | 11 | 1 | some allergenes |
| 1 | 12 | 1 | other ingredients |
| 2 | 12 | 1 | other allergenes |
+--------+------------+---------+-------------------+
I want to combine these rows first. Running this second function on table 'ps_extraproducttab_product_lang' does exactly that:
FUNCTION 2
SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg'
FROM ps_extraproducttab_product_lang t1, ps_extraproducttab_product_lang t2
WHERE t1.id_product = t2.id_product
AND t1.id_Tab = '1'
AND t2.id_Tab = '2'
It outputs:
+------------+-------------------+------------------+
| id_product | ingred | allerg |
+------------+-------------------+------------------+
| 11 | some ingredients | some allergenes |
| 12 | other ingredients | other allergenes |
+------------+-------------------+------------------+
I used this source, privided by Akina: https://dba.stackexchange.com/questions/236692/combining-multiple-rows-into-a-single-row-with-multiple-columns ( I still need to find out how to extend this code to a 3th and 4th id_Tab, although that is not the topic of my current question )
I am unable to integrate the above in a single query that would result into:
+------------+---------------+-------------+-----------+-------------------+-------------------+
| id_product | ean13 | weight | name | ingred | allerg | |
+------------+---------------+-------------+-----------+-------------------+-------------------+
| 11 | 0000000000001 | 1000.000000 | product_A | some ingredients | some allergenes |
| 12 | 0000000000002 | 1500.000000 | product_B | other ingredients | other allergenes |
+------------+---------------+-------------+-----------+-------------------+-------------------+
How would you build a single SQL-query to get the above result?
Any help is appreciated!
Consider multiple CTEs if using latest versions of MySQL/MariaDB to your Prestashop platform. Be sure to use explicit joins (not implicit as DBA SE link uses) and avoid a, b, c table aliasing. Extend the self-joins to ps_extraproducttab_product_lang
for the 3rd and 4th categories.
WITH ew AS
(SELECT p.id_product, p.ean13, p.weight, pl.name
FROM ps_product AS p
INNER JOIN ps_product_lang AS pl
ON p.id_product = pl.id_product
), ia AS
(SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg'
, t3.content AS 'thirdcat', t4.content AS 'fourthcat'
FROM ps_extraproducttab_product_lang t1
INNER JOIN ps_extraproducttab_product_lang t2
ON t1.id_product = t2.id_product
AND t1.id_Tab = '1' AND t2.id_Tab = '2'
INNER JOIN ps_extraproducttab_product_lang t3
ON t1.id_product = t3.id_product AND t3.id_Tab = '3'
INNER JOIN ps_extraproducttab_product_lang t4
ON t1.id_product = t4.id_product AND t4.id_Tab = '4'
)
SELECT ew.id_product, ew.ean13, ew.weight, ew.name
, ia.ingred, ia.allerg, ia.thirdcat, ia.fourthcat
FROM ew
INNER JOIN ia
ON ew.id_product = ia.id_product
For earlier versions of MySQL (pre v8.0) or MariaDB (pre v10.2), use subqueries:
SELECT ew.id_product, ew.ean13, ew.weight, ew.name
, ia.ingred, ia.allerg, ia.thirdcat, ia.fourthcat
FROM
(SELECT p.id_product, p.ean13, p.weight, pl.name
FROM ps_product AS p
INNER JOIN ps_product_lang AS pl
ON p.id_product = pl.id_product
) ew
INNER JOIN
(SELECT t1.id_product, t1.content AS 'ingred', t2.content AS 'allerg'
, t3.content AS 'thirdcat', t4.content AS 'fourthcat'
FROM ps_extraproducttab_product_lang t1
INNER JOIN ps_extraproducttab_product_lang t2
ON t1.id_product = t2.id_product
AND t1.id_Tab = '1' AND t2.id_Tab = '2'
INNER JOIN ps_extraproducttab_product_lang t3
ON t1.id_product = t3.id_product AND t3.id_Tab = '3'
INNER JOIN ps_extraproducttab_product_lang t4
ON t1.id_product = t4.id_product AND t4.id_Tab = '4'
) ia
ON ew.id_product = ia.id_product
Thank you for stepping in! I am getting an error on both solutions: MySQL said: #1060 - Duplicate column name 'id_product'. Do these phpmyadmin details help? Server type: MariaDB, Server version: 10.3.27-MariaDB - MariaDB Server, Protocol version: 10, Database client version: libmysql - 5.6.43, PHP extension: mysqli curl mbstring, PHP version: 7.3.6
Please try again. I redundantly included two references of
id_product
in theew
resultset.Parfait, people like you make the internet worthwhile! Thank you, this works. I will study your answer and create a better understanding of MySQL.
Haha...nice to hear and happy to help and happy coding! BTW - Technically, you are using MariaDB, cousin of MySQL (both originally by same creator whose daughters are named My and Maria) and so MariaDB uses many of the MySQL coding APIs such as PHP's prestashop. So be sure to use MariaDB docs in your SQL learning.
Optional: Change the '... ew INNER JOIN (SELECT ...' into '... ew LEFT JOIN (SELECT ...' if you want to output rows with empty t#.id_Tab as NULL