By letsdothis


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.

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:

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 AND t2.meta_key = '_billing_first_name'
LEFT JOIN test_postmeta t3 
ON t1.ID = t3.post_id AND t3.meta_key = '_billing_last_name'
GROUP BY t1.ID 
ORDER BY t1.post_date DESC LIMIT 20")

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:

  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 
    LEFT JOIN test_postmeta t3  ON t1.ID = t3.post_id 
    WHERE t3.meta_key = '_billing_last_name' and t2.meta_key = '_billing_first_name'
    GROUP BY t1.ID 
    ORDER BY t1.post_date DESC LIMIT 20")

It might be worth reading a little bit about SQL Joins and WHERE statements.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

23 Answered Questions

[SOLVED] Does Python have a ternary conditional operator?

16 Answered Questions

[SOLVED] What are metaclasses in Python?

42 Answered Questions

[SOLVED] How do I merge two dictionaries in a single expression?

25 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

63 Answered Questions

[SOLVED] Calling an external command from Python

26 Answered Questions

[SOLVED] How do I concatenate two lists in Python?

10 Answered Questions

[SOLVED] Does Python have a string 'contains' substring method?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

3 Answered Questions

[SOLVED] How to get the max of two values in MySQL?

  • 2009-10-14 11:25:39
  • Mask
  • 112256 View
  • 276 Score
  • 3 Answer
  • Tags:   mysql max

Sponsored Content