MySql CONCATENATE result of UNION

Question

I want to have a result concatenate from a result of a union of query... my code is this:

SELECT GROUP_CONCAT(rifDoc), idUser, user, dateDoc
FROM
(
SELECT GROUP_CONCAT(CAR.rifDoc) AS rifDoc, CAR.idUser, CAR.user, CARDETT.dateDoc
FROM car AS CAR, carDett AS CARDETT 
WHERE CAR.id>0 CAR.id=CARDETT.idDoc CARDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc

UNION ALL

SELECT GROUP_CONCAT(BK.rifDoc) AS rifDoc, BK.idUser, BK.user, GROUP_CONCAT(BK.inUso) AS inUso, GROUP_CONCAT(BK.inCarico) AS inCarico, BKDETT.dateDoc
FROM bike AS BK, bikeDett AS BKDETT 
WHERE BK.id>0 AND BK.id=BKDETT.idDoc AND BKDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc
 )

GROUP BY idUser, dateDoc

But I have an error like this:

#1248 - Every derived table must have its own alias

Someone have a solutions?

Answer 1

You are missing an alias for the inline view or subquery as the error states like

SELECT GROUP_CONCAT(rifDoc), idUser, `user`, dateDoc
FROM
(
SELECT GROUP_CONCAT(CAR.rifDoc) AS rifDoc, CAR.idUser, CAR.user, CARDETT.dateDoc
FROM car AS CAR, carDett AS CARDETT 
WHERE CAR.id>0 CAR.id=CARDETT.idDoc CARDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc

UNION ALL

SELECT GROUP_CONCAT(BK.rifDoc) AS rifDoc, BK.idUser, BK.user, GROUP_CONCAT(BK.inUso) AS inUso, GROUP_CONCAT(BK.inCarico) AS inCarico, BKDETT.dateDoc
FROM bike AS BK, bikeDett AS BKDETT 
WHERE BK.id>0 AND BK.id=BKDETT.idDoc AND BKDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc
 ) XXX     <--- here

Answer 2

Every derived table (AKA sub-query) must indeed have an alias. I.e. each query in brackets must be given an alias (AS whatever), which can the be used to refer to it in the rest of the outer query. I am not sure but I think your code should be like this

SELECT GROUP_CONCAT(rifDoc), idUser, user, dateDoc
FROM
(
SELECT GROUP_CONCAT(CAR.rifDoc) AS rifDoc, CAR.idUser, CAR.user, CARDETT.dateDoc
FROM car AS CAR, carDett AS CARDETT 
WHERE CAR.id>0 CAR.id=CARDETT.idDoc CARDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc) As T1

UNION ALL

(SELECT GROUP_CONCAT(BK.rifDoc) AS rifDoc, BK.idUser, BK.user, GROUP_CONCAT(BK.inUso) AS inUso, GROUP_CONCAT(BK.inCarico) AS inCarico, BKDETT.dateDoc
FROM bike AS BK, bikeDett AS BKDETT 
WHERE BK.id>0 AND BK.id=BKDETT.idDoc AND BKDETT.dateDoc<='2017-01-31' 
GROUP BY idUser, dateDoc
 )As T2)

GROUP BY idUser, dateDoc

not sure about the code but the solution is (As anythin)