MySQL derived table with join

Question

In the following query I'm having a problem when it comes to returning the right value for count2.

What I need is to get the number of rows from table2 which could easily be done by using a derived table t:

SELECT name, 
(SELECT COUNT(*) FROM `table1`) AS count1,
(SELECT COUNT(*) FROM (
    SELECT COUNT(*) FROM `table2` t2) WHERE t2.user = prf.user)
) t AS count2,
(SELECT SUM(a) FROM `table3`) AS count3
FROM `profiles` prf
WHERE 1=1
AND prf.user = 1

The problem is that the WHERE t2.user = prf.user statement fails as the prf table is outside the subquery's scope.

How can I achieve the above?

EDIT: I'm adding the actual query in case it's helpful for getting a better grasp:

SELECT PRF.BranchID, PRF.user_id, CONCAT_WS(" ",PRF.lastname,PRF.firstname) Synergatis, 
( SELECT COUNT(*) FROM Actions A JOIN Requests R ON R.RequestID=A.RequestID WHERE A.ActionStatus = 302 AND A.UserOwner = PRF.user_id AND A.ActionDate BETWEEN '2015-06-01' AND '2015-06-10' ) AS energeies, 
( SELECT COUNT(DISTINCT RPP.RequestID) FROM VW_Xartofylakio_Synergati VV JOIN Requests_Prop RPP ON RPP.PropertyID = VV.PropertyID JOIN Requests R ON R.RequestID = RPP.RequestID WHERE VV.CurrUsr = PRF.user_id AND R.ModifyTime BETWEEN '2015-06-01' AND '2015-06-10' ) AS zitiseis_eidikes, 
( SELECT COUNT(DISTINCT(CustomerID)) FROM Demo_Orders_M WHERE DemoOrderStatus=253 AND USER=PRF.user_id AND DemoOrderDate BETWEEN '2015-06-01' AND '2015-06-10' ) AS endiaferomenoi, 
( SELECT COUNT(*) AS cnt FROM Demo_Orders_M DOM JOIN Actions A ON DOM.DemoOrderID = A.DemoOrderID WHERE DOM.User = PRF.user_id AND DOM.DemoOrderStatus = 253 AND A.ActionDate BETWEEN '2015-06-01 14:56:19' AND '2015-06-30 14:56:19' GROUP BY DOM.CustomerID, DOM.User HAVING COUNT(*) > 1 ) AS anakykl_endiaf, 
( SELECT COUNT(*) FROM Demo_Orders_M DOM WHERE DOM.`User`=PRF.user_id AND DemoOrderStatus = 253 AND DOM.DemoOrderDate BETWEEN '2015-06-01' AND '2015-06-10' ) AS epideixeis, 
( SELECT COUNT(DISTINCT(DOD.PropertyID)) AS PropertyID FROM Demo_Orders_M DOM JOIN Demo_Orders_D DOD ON DOM.DemoOrderID = DOD.DemoOrderID JOIN Actions A ON DOD.DemoOrderID = A.DemoOrderID WHERE DOM.DemoOrderStatus = 253 AND DOM.User = PRF.user_id AND A.ActionDate BETWEEN '2015-06-01' AND '2015-06-10' ) AS monadika_akinita
FROM tbl_profiles PRF 
WHERE 1=1 
AND PRF.user_id IN (
    SELECT a.user_id FROM tbl_profiles a WHERE a.user_id IN ('248','1159','486','183') 
    OR a.GroupID IN (SELECT b.GroupID FROM L_Groups b WHERE b.ManagerID IN ('248','1159','486','183')) 
    ) 
ORDER BY PRF.user_id

The subquery I'm referring to is the one that returns the result as anakykl_endiaf.

Answer 1

I suspect it is not because of prf table, it is because of t2 table... There are no restrictions to use outer alias in inner subqueries because there are such a thing like correlated subquery. Your problem is that you have the opposite case here: you are referring inner alias in outer query.

(SELECT COUNT(*) 
 FROM (SELECT COUNT(*) FROM `table2` t2) WHERE t2.user = prf.user)

Why are you selecting count twice here? You can change to this:

(SELECT COUNT(*) 
 FROM (SELECT COUNT(*) FROM `table2` t2 WHERE t2.user = prf.user))

or this:

(SELECT COUNT(*) 
 FROM `table2` t2 WHERE t2.user = prf.user)

Answer 2

A suggestion to try.

You have sub queries in the SELECT, and in this case they must each only return a single row. For some reason (which we can't really tell without test data) one of these is returning more than 1 row, hence failing.

As an interim step, change the query to join against the sub queries, which should make it more obvious when there are duplicates (and may also be quite a bit more efficient, depending on the data).

Something like this (not tested so probably a few typos):-

SELECT PRF.BranchID, 
    PRF.user_id, 
    CONCAT_WS(" ",PRF.lastname,PRF.firstname) Synergatis, 
    ar.energeies,
    vrr.zitiseis_eidikes,
    m.endiaferomenoi, 
    ae.anakykl_endiaf,
    d.epideixeis,
    ddd.monadika_akinita
FROM tbl_profiles PRF 
LEFT OUTER JOIN
( 
    SELECT A.UserOwner AS DomUser, COUNT(*) AS energeies
    FROM Actions A 
    JOIN Requests R ON R.RequestID=A.RequestID 
    WHERE A.ActionStatus = 302 
    AND A.ActionDate BETWEEN '2015-06-01' AND '2015-06-10' 
    GROUP BY A.UserOwner
) ar 
ON ar.DomUser = PRF.user_id 
LEFT OUTER JOIN
( 
    SELECT VV.CurrUsr AS DomUser, COUNT(DISTINCT RPP.RequestID) AS zitiseis_eidikes
    FROM VW_Xartofylakio_Synergati VV 
    JOIN Requests_Prop RPP ON RPP.PropertyID = VV.PropertyID 
    JOIN Requests R ON R.RequestID = RPP.RequestID 
    WHERE R.ModifyTime BETWEEN '2015-06-01' AND '2015-06-10' 
    GROUP BY VV.DomUser
) vrr 
ON vrr.DomUser = PRF.user_id 
LEFT OUTER JOIN
( 
    SELECT `USER` AS DomUser, COUNT(DISTINCT(CustomerID)) AS endiaferomenoi
    FROM Demo_Orders_M 
    WHERE DemoOrderStatus=253 
    AND DemoOrderDate BETWEEN '2015-06-01' AND '2015-06-10' 
    GROUP BY DomUser
) m
ON PRF.user_id  = m.DomUser
LEFT OUTER JOIN
( 
    SELECT DOM.CustomerID, DOM.`User` AS DomUser, COUNT(*) AS anakykl_endiaf 
    FROM Demo_Orders_M DOM 
    JOIN Actions A ON DOM.DemoOrderID = A.DemoOrderID 
    WHERE DOM.DemoOrderStatus = 253 
    AND A.ActionDate BETWEEN '2015-06-01 14:56:19' AND '2015-06-30 14:56:19' 
    GROUP BY DOM.CustomerID, DOM.DomUser 
    HAVING COUNT(*) > 1 
) ae
ON PRF.user_id  = ae.DomUser
LEFT OUTER JOIN
( 
    SELECT DOM.`User` AS DomUser, COUNT(*) AS epideixeis
    FROM Demo_Orders_M DOM 
    WHERE DemoOrderStatus = 253 
    AND DOM.DemoOrderDate BETWEEN '2015-06-01' AND '2015-06-10' 
    GROUP BY DOM.DomUser 
) d 

EDIT

If you just want a count of the number of customerID fields for a user in the anakykl_endiaf field then change it to doing a count of distinct customerIDs. Ie, for the above query I have done change it to:-

LEFT OUTER JOIN
( 
    SELECT DOM.`User` AS DomUser, COUNT(DISTINCT DOM.CustomerID) AS anakykl_endiaf 
    FROM Demo_Orders_M DOM 
    JOIN Actions A ON DOM.DemoOrderID = A.DemoOrderID 
    WHERE DOM.DemoOrderStatus = 253 
    AND A.ActionDate BETWEEN '2015-06-01 14:56:19' AND '2015-06-30 14:56:19' 
    GROUP BY DOM.DomUser 
    HAVING COUNT(*) > 1 
) ae