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.

The subquery returns 3 rows which is correct according to my data, however I need to return the row count rather than the rows themself. The error I'm getting is 'Status 1242 - Subquery returns more than 1 row'. — bikey77, Jun 24 '15 at 08:13
This query is part of a reporting system where none of the conditions are granted as they depend on the fields the user uses to create the report. Therefore, I use where 1=1 to begin adding conditions dynamically. — bikey77, Jun 24 '15 at 08:22
Can you post the data on Demo_Orders_M and Actions that demonstrate the problem. To be it looks perfectly possible depending on the data that the sub query does return multiple rows for a user_id (for example, if Demo_Orders_M had multiple rows for the same user, but with different customerID values for that user) — Kickstart, Jun 24 '15 at 08:37
That's the case. It does return multiple rows which is correct, however I need to be able to return the row count but at the same being able to join DOM.User on PRF.user_id which is not possible with the use of derived tables. — bikey77, Jun 24 '15 at 08:41
Still struggling? To expand upon suggestions already made: If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jun 24 '15 at 08:50
It is possible. The error is that there are multiple rows returned. Having a query in the select like that means it can only return a single row. You need the query to only return a single row for that user. — Kickstart, Jun 24 '15 at 08:56
@Kickstart: That is actually the reason why I posted in the first place. Any suggestions on how I could return only a single row containing the number of rows i.e. 3 instead of 3 rows? — bikey77, Jun 24 '15 at 09:20
Change the query to remove customerid from the GROUP BY clause, and use COUNT(DISTINCT DOM.CustomerID) — Kickstart, Jun 24 '15 at 09:26
With this modification, it returns a single row but the result is wrong as the GROUP BY on both fields is required for the proper result count. — bikey77, Jun 29 '15 at 07:57

Giorgi Nakeuri · Answer 1 · 2015-06-24T08:14:25.310

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)

edited Jun 24 '15 at 08:14

answered Jun 24 '15 at 08:08

Giorgi Nakeuri

35,155
8
47
75

I'm selecting count twice as I need to initially perform a count based on a HAVING count > 1 criteria and then count the rows that match this condition. The actual query is far more complex, the above is simply a mock up. – bikey77 Jun 24 '15 at 08:15

Kickstart · Answer 2 · 2015-06-24T09:25:16.190

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

@GiorgiNakeuri - He is selecting counts with a GROUP BY clause, but the GROUP BY clause specifies 2 fields while only one is checked for a match in the sub query. Hence if on the Demo_Orders_M table a user has 2 records each with a different CustomerID field then the sub query will return 2 counts for that user, hence the query would fail. — Kickstart, Jun 24 '15 at 09:15

MySQL derived table with join

2 Answers2