2019-12-02 22:25:01 8 Comments
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.
Related Questions
Sponsored Content
28 Answered Questions
[SOLVED] How can I prevent SQL injection in PHP?
- 2008-09-12 23:55:00
- Andrew G. Johnson
- 1668887 View
- 2776 Score
- 28 Answer
- Tags: php mysql sql security sql-injection
23 Answered Questions
[SOLVED] Does Python have a ternary conditional operator?
- 2008-12-27 08:32:18
- Devoted
- 1804876 View
- 5625 Score
- 23 Answer
- Tags: python operators ternary-operator conditional-operator
16 Answered Questions
[SOLVED] What are metaclasses in Python?
- 2008-09-19 06:10:46
- e-satis
- 752623 View
- 5439 Score
- 16 Answer
- Tags: python oop metaclass python-datamodel
42 Answered Questions
[SOLVED] How do I merge two dictionaries in a single expression?
- 2008-09-02 07:44:30
- Carl Meyer
- 1692562 View
- 4381 Score
- 42 Answer
- Tags: python dictionary merge
25 Answered Questions
[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?
- 2008-09-01 22:36:06
- Chris de Vries
- 2281983 View
- 4515 Score
- 25 Answer
- Tags: sql database join inner-join outer-join
63 Answered Questions
[SOLVED] Calling an external command from Python
- 2008-09-18 01:35:30
- freshWoWer
- 3264702 View
- 4592 Score
- 63 Answer
- Tags: python shell terminal subprocess command
26 Answered Questions
[SOLVED] How do I concatenate two lists in Python?
- 2009-11-12 07:04:09
- y2k
- 2223872 View
- 2274 Score
- 26 Answer
- Tags: python list concatenation
10 Answered Questions
33 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 4105944 View
- 3553 Score
- 33 Answer
- Tags: sql sql-server tsql select
2 comments
@forpas 2019-12-02 22:31:04
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.
@wundermahn 2019-12-02 22:29:30
Your
SQL
syntax is incorrect. Try this:It might be worth reading a little bit about
SQL
Joins and WHERE statements.