Looking for some advice to complete custom mysql query against 3 different tables

Question

I would like to create a query to populate a table dynamically by querying the 3 databases that contain relevant information about their entry.

wp_gf_entry, wp_gf_entry_meta, and (ideally) with wp_posts

The query is working with wp_gf_entry and the wp_gf_entry_meta tables.

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
    t2.`form_id` = 71
    AND t2.`is_read` = 1 AND t2.`status` = 'active' 
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6')
  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

This works well, IF the entry has been 'read' and the entry hasn't been 'trashed'.

+-----+-----------+----------+-------+------------------------------+
| id  | firstname | lastname | state |           division           |
+-----+-----------+----------+-------+------------------------------+
| 166 | Steve     | Jobs     | QLD   | (null)                       |
| 253 | Bill      | Clinton  | NSW   | (null)                       |
| 427 | Maria     | McOldguy | VIC   | Grand Master's (65 and over) |
| 447 | Some      | Bloke    | NSW   | Master's (60-64)             |
+-----+-----------+----------+-------+------------------------------+

But this query still requires a human to interact with the website multiple times a day to process entries (marking them read) - because entries are still written to the database regardless of whether or not the entry has been successfully paid for.

This is where I would like to query the wp_posts table using the woocommerce_order_number key and value pair stored in the wp_gf_entry_meta row for each entry.

So modified the query to this to pull the woocommerce_order_number into the array and what I thought would be something that could be queried:

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
t2.`form_id` = 71
    AND t2.`is_read` = 1 AND t2.`status` = 'active'
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')

  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

The above query gives me a column with the woocommerce order id:

+-----+------+-----------+----------+-------+------------------------------+
| id  | wcID | firstname | lastname | state |           division           |
+-----+------+-----------+----------+-------+------------------------------+
| 166 |    1 | Steve     | Jobs     | QLD   | (null)                       |
| 253 |    2 | Bill      | Clinton  | NSW   | (null)                       |
| 427 |    3 | Maria     | McOldguy | VIC   | Grand Master's (65 and over) |
| 447 |    4 | Some      | Bloke    | NSW   | Master's (60-64)             |
+-----+------+-----------+----------+-------+------------------------------+

However, I'd like to take the human factor out by adding the following to the WHERE statement

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
t2.`form_id` = 71
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')

        AND t1.`meta_value` IN (SELECT t3.ID FROM wp_posts t3 
                WHERE t3.ID = [wcID]
                    AND t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') 
                    AND t3.`post_type` = 'shop_order' )
  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

Results in this error message "Warning: #1292 Truncated incorrect DOUBLE value: 'wcID'".

And I'm not quite sure how to finesse this final AND statement.

SQL Fiddle location

Is there a way to query the wp_posts table and ID column using the woocommerce_order_number alias wcID?

Answer 1

Check

SELECT 
  t2.id, 
  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM  wp_gf_entry_meta t1 
LEFT JOIN wp_gf_entry t2 
    ON t1.entry_id = t2.id
LEFT JOIN ( SELECT t3.ID 
            FROM wp_posts t3 
            WHERE t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') 
              AND t3.`post_type` = 'shop_order' ) t4 
    ON t1.`meta_key` = 'woocommerce_order_number' AND t1.meta_value = t4.ID
WHERE t2.`form_id` = 71
  AND t2.`is_read` = 1 
  AND t2.`status` = 'active'
  AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')
GROUP BY t2.id

fiddle