MySql link table in union query

Question

I need to select some specific sum from union query to 3 table that should be
available in column field, but I see an error Error Code: 1054. Unknown column 'm.Id' in 'where clause' It look like M.id in union sub query is not visible. how I can make it work, without functions, only via query.

SELECT
    m.COL1,
    m.COL2,
    p.Id,
    (
        SELECT 
            sum(cp) 
        FROM (
                SELECT 
                     count(*) as cp 
                FROM 
                     T1 as o
                WHERE 
                     o.st = 4 
                     AND o.LId = m.Id

                UNION ALL

                SELECT 
                     count(*) as cp 
                FROM 
                     T2 as oh
                WHERE 
                     oh.st = 0 
                     AND oh.LId = m.Id

                UNION ALL

                SELECT 
                    count(*) as cp 
                FROM 
                    T3 as os
                WHERE
                    os.st = 4 
                    AND os.LId = m.Id

            ) as PP 
        ) as IsFB

    FROM 
        TM as m
        JOIN TME as mx ON m.Id = mx.MId
        JOIN TP as p ON m.CID = p.PID

Answer 1

Its hard to answer without you providing a little more information on what exactly you want

but, for a start do the joins in your sub queries , something like:

            SELECT 
                 TM.COL1, TM.COL2, TM.id, count(*) as cp 
            FROM 
                 T1
            JOIN 
                 TM
            ON   
                 T1.LId = TM.id
            GROUP BY 
                 TM.COL1, TM.COL2, TM.id

Then you will be able to join this in an outer query, something like

SELECT
    UU.COL1,
    UU.COL2,
    TP.ID,
    sum(UU.cp)
FROM (
  ** union query goes here
) UU
JOIN TP ON UU.id = TP.PID
GROUP BY 
  UU.COL1, UU.COL2, TP.ID