MySQL referencing other SELECTS in SELECT subquery

I'm trying to create a query to rank products based on onsite browsing habits (I have provided a simplified non-dynamic query example below).

The issue I'm having is with the UNIONs in the SELECT subquery referencing other columns.

Does anyone know a clever workaround for this?

SELECT p.*, pi.piid, c.title AS cat_title, c.fn AS cat_fn, b.fn AS brand_fn, b.title AS brand_title,
(
SELECT SUM(prod_rank) AS rank FROM (
    (
        SELECT 2.95 AS prod_rank FROM prod_link_cat WHERE pid = p.pid AND link_cid = 1
    ) UNION ALL (
        SELECT 2.8 AS prod_rank FROM prod_link_cat WHERE p id = p.pid AND link_cid = 3
    ) UNION ALL (
        SELECT 0.5 AS prod_rank FROM prod_link_cat WHERE pid = p.pid AND link_cid = 2
    )
) AS tbl1
) AS rank
FROM prod p
LEFT JOIN prod_link_cat plc ON plc.pid = p.pid AND plc.position = 1
LEFT JOIN cat c ON plc.link_cid = c.cid AND c.live = 1
LEFT JOIN brand b ON b.bid = p.bid AND b.live = 1
LEFT JOIN prod_link_prod_img plpi ON plpi.pid = p.pid AND plpi.position = 1
LEFT JOIN prod_img pi ON pi.piid = plpi.link_piid AND pi.live = 1
WHERE p.live = 1
GROUP BY p.pid
ORDER BY (RAND() * rank)
LIMIT 20

Solution

To remove your inline field query (with unions), I would rewrite it and move it as a left-join based on same criteria as your outer "prod" criteria of p.Live = 1. No sense in querying everything else where p.Live = something else.

SELECT 
      p.*, 
      pi.piid, 
      c.title AS cat_title, 
      c.fn AS cat_fn, 
      b.fn AS brand_fn, 
      b.title AS brand_title, 
      COALESCE( PreQuery.ProdRankSum, 0 ) as ProdRankSum
   FROM 
      prod p 
         LEFT JOIN 
         ( SELECT
                 p2.id,
                 SUM( if( plc.link_cid = 1, 2.95, 0.00 )
                    + if( plc.link_cid = 2,  .50, 0.00 )
                    + if( plc.link_cid = 3, 2.80, 0.00 )) ProdRankSum
              FROM 
                 prod p2
                    JOIN prod_link_cat plc
                       ON p2.ID = plc.pid
                      AND plc.link_cid in ( 1, 2, 3 )
              WHERE
                 p2.Live = 1
              GROUP BY
                 p2.id ) PreQuery
            ON p.id = PreQuery.id

         LEFT JOIN prod_link_cat plc 
            ON p.pid = plc.pid AND plc.position = 1 
            LEFT JOIN cat c 
              ON plc.link_cid = c.cid AND c.live = 1 

         LEFT JOIN brand b 
            ON p.bid = b.bid AND b.live = 1 

         LEFT JOIN prod_link_prod_img plpi 
            ON p.pid = plpi.pid  AND plpi.position = 1 
            LEFT JOIN prod_img pi 
              ON plpi.link_piid = pi.piid AND pi.live = 1
   WHERE 
      p.live = 1 
   GROUP BY 
      p.pid 
   ORDER BY 
      (RAND() * COALESCE( PreQuery.ProdRankSum, 0 )) 
   LIMIT 20

Now, after all this, you will probably have to do something about the rank, such as set the COALESCE() value other than 0... such as .001 otherwise, it will always be a zero for those products that do not have a product link cat... and ordering by a random time 0 will always be zero and thus to the top. (Or change to ORDER BY ... DESC)

HOWEVER, if you ONLY WANT those products that explicitly HAVE a link category of 1, 2 or 3, I would slightly rewrite this query too. Let me know.

Additionally, your original query is doing left join to product link cat for the position = 1, then to cat where cat is LIVE = 1... and left joins on the others too. Did you INTEND to do left joins?