SQL Query Conditional | WordPress.org

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'

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'