SQL Query Conditional

  • Resolved frankiewebs

    (@frankiewebs)


    2 years, 3 months ago

    Greetings. I used pods to create the custom lost type “Lot”. Each lot has a field called “lot_status”. I am trying to write an SQL query to show all lots with a specific lot status. When I run it with no conditions, I get all the results. However, when I try to add a conditional WHERE clause, I get an ‘unknown column’ error. I have tried lots of different combinations of meta_value and lot_status, but I keep getting the unknown column error.

    SELECT posts_lot.post_title AS lot_post_title,
       posts_lot.ID AS lot_ID,
       posts_lot.post_content AS lot_post_content,
       lot_meta_lot_status_tbl.meta_value AS lot_meta_lot_status,
       lot_meta_room_tbl.meta_value AS lot_meta_room,
       lot_meta_product_tbl.meta_value AS lot_meta_product
FROM fzMCGsSFposts AS posts_lot
  INNER JOIN (SELECT lot_meta_lot_status_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_lot_status_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_lot_status_tbl_posts   ON lot_meta_lot_status_tbl_postmeta.post_id = lot_meta_lot_status_tbl_posts.ID   AND lot_meta_lot_status_tbl_posts.post_type = 'lot') AS lot_meta_lot_status_tbl
     ON lot_meta_lot_status_tbl.meta_key = 'lot_status' AND lot_meta_lot_status_tbl.id = posts_lot.ID 
  INNER JOIN (SELECT lot_meta_room_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_room_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_room_tbl_posts   ON lot_meta_room_tbl_postmeta.post_id = lot_meta_room_tbl_posts.ID   AND lot_meta_room_tbl_posts.post_type = 'lot') AS lot_meta_room_tbl
     ON lot_meta_room_tbl.meta_key = 'room' AND lot_meta_room_tbl.id = posts_lot.ID 
  INNER JOIN (SELECT lot_meta_product_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_product_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_product_tbl_posts   ON lot_meta_product_tbl_postmeta.post_id = lot_meta_product_tbl_posts.ID   AND lot_meta_product_tbl_posts.post_type = 'lot') AS lot_meta_product_tbl
     ON lot_meta_product_tbl.meta_key = 'product' AND lot_meta_product_tbl.id = posts_lot.ID 
WHERE 1=1 
   AND posts_lot.post_content > '0'
   AND posts_lot.post_type = 'lot'
   AND lot_status.meta_value = '1199'

    Can you help me find the proper syntax to target the lot_status field with a where function?

    Thanks very much! Let me know if you need more information.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter frankiewebs

    (@frankiewebs)

    Also tried a slightly different approach that yielded the same unknown column error.

    SELECT posts_lot.post_title AS lot_post_title,
       posts_lot.ID AS lot_ID,
       posts_lot.post_content AS lot_post_content,
       lot_meta_lot_status_tbl.meta_value AS lot_meta_lot_status,
       lot_meta_room_tbl.meta_value AS lot_meta_room,
       lot_meta_product_tbl.meta_value AS lot_meta_product
FROM fzMCGsSFposts AS posts_lot
  INNER JOIN (SELECT lot_meta_lot_status_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_lot_status_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_lot_status_tbl_posts   ON lot_meta_lot_status_tbl_postmeta.post_id = lot_meta_lot_status_tbl_posts.ID   AND lot_meta_lot_status_tbl_posts.post_type = 'lot') AS lot_meta_lot_status_tbl
     ON lot_meta_lot_status_tbl.meta_key = 'lot_status' AND lot_meta_lot_status_tbl.id = posts_lot.ID 
     AND lot_meta_lot_status = '1199'
  INNER JOIN (SELECT lot_meta_room_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_room_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_room_tbl_posts   ON lot_meta_room_tbl_postmeta.post_id = lot_meta_room_tbl_posts.ID   AND lot_meta_room_tbl_posts.post_type = 'lot') AS lot_meta_room_tbl
     ON lot_meta_room_tbl.meta_key = 'room' AND lot_meta_room_tbl.id = posts_lot.ID 
  INNER JOIN (SELECT lot_meta_product_tbl_posts.ID as id, meta_value, meta_key  FROM fzMCGsSFpostmeta AS lot_meta_product_tbl_postmeta  INNER JOIN fzMCGsSFposts AS lot_meta_product_tbl_posts   ON lot_meta_product_tbl_postmeta.post_id = lot_meta_product_tbl_posts.ID   AND lot_meta_product_tbl_posts.post_type = 'lot') AS lot_meta_product_tbl
     ON lot_meta_product_tbl.meta_key = 'product' AND lot_meta_product_tbl.id = posts_lot.ID 
WHERE 1=1 
   AND posts_lot.post_type = 'lot'
    Plugin Contributor Scott Kingsley Clark

    (@sc0ttkclark)

    Try replacing:

    AND lot_status.meta_value = '1199'

    with:

    AND lot_meta_lot_status_tbl.meta_value = '1199'

    Also, I’d probably write that query in simpler way like this:

    SELECT 
	posts_lot.post_title AS lot_post_title,
	posts_lot.ID AS lot_ID,
	posts_lot.post_content AS lot_post_content,
	lot_meta_lot_status_tbl.meta_value AS lot_meta_lot_status,
	lot_meta_room_tbl.meta_value AS lot_meta_room,
	lot_meta_product_tbl.meta_value AS lot_meta_product
FROM fzMCGsSFposts AS posts_lot
LEFT JOIN fzMCGsSFpostmeta AS lot_meta_lot_status_tbl
	ON lot_meta_lot_status_tbl.post_id = posts_lot.ID
	AND lot_meta_lot_status_tbl.meta_key = 'lot_status'
LEFT JOIN fzMCGsSFpostmeta AS lot_meta_room_tbl
	ON lot_meta_room_tbl.post_id = posts_lot.ID
	AND lot_meta_room_tbl.meta_key = 'room'
LEFT JOIN fzMCGsSFpostmeta AS lot_meta_product_tbl
	ON lot_meta_product_tbl.post_id = posts_lot.ID
	AND lot_meta_product_tbl.meta_key = 'product'
WHERE
	posts_lot.post_content != ''
	AND posts_lot.post_type = 'lot'
	AND lot_meta_lot_status_tbl.meta_value = '1199'
    Thread Starter frankiewebs

    (@frankiewebs)

    That did the trick, Scott. I also used the rewritten query you supplied. Thank you for your lightning fast response!

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘SQL Query Conditional’ is closed to new replies.