I have some problems with a SQL for Python that I hope you can help me with – I’m trying to retrieve some data from wordpress/woocommerce.
My code:
cursor.execute("
SELECT t1.ID, t1.post_date, t2.meta_value AS first_name, t3.meta_value AS last_name
FROM test_posts t1
LEFT JOIN test_postmeta t2
ON t1.ID = t2.post_id
WHERE t2.meta_key = '_billing_first_name' and t2.post_id = t1.ID
LEFT JOIN test_postmeta t3
ON t1.ID = t3.post_id
WHERE t3.meta_key = '_billing_last_name' and t3.post_id = t1.ID
GROUP BY t1.ID
ORDER BY t1.post_date DESC LIMIT 20")
I’m getting the following error:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN test_postmeta t3 ON t1.ID = t3.post_id WHERE t3.meta_key = '_billing' at line 1
What am I doing wrong?
Thanks in advance.
2
Answers
Your
SQL
syntax is incorrect. Try this:It might be worth reading a little bit about
SQL
Joins and WHERE statements.There should be only 1 WHERE clause before GROUP BY.
But since you use LEFT joins, setting a condition on the right table like
t2.meta_key = '_billing_first_name'
you get an INNER join instead because you reject unmatched rows.So set all the conditions in the ON clauses:
Although this query may be syntactically correct for MySql, it does not make sense to use GROUP BY since you do not do any aggregation.