Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Woocommerce – Python SQL – two left joins

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search