Help with UNION

[mellis95]

I am not sure if this should be a mysql question or 3rd party php scripts or AJAX, so if it needs to move to a different board, I understand.

 

I am trying to query four tables based on a text field. I have never tried this before, but a bit of searching here and on Google prompted me to try a UNION ALL query. The query below works fine on a command line and produces the desired result:

(SELECT 'e' AS TableName,eval_id AS id,discipline_id AS discipline FROM tbl_evaluation)
UNION ALL (SELECT 'p' AS TableName,pocid AS id,discipline_id AS discipline FROM tbl_poc)
UNION ALL (SELECT 'd' AS TableName,dcid AS id,discipline_id AS discipline FROM tbl_dc)
UNION ALL (SELECT 't' AS TableName,tp2id AS id,discipline_id AS discipline FROM tbl_tp2)

 

However, I am trying to display the result in a Rico LiveGrid, which convolutes the original query by add "FROM" to the front end and a LIMIT CLAUSE on the end of the query. So if I use the query that works on the command line, with Rico LiveGrid, I get the following output from the xmlResponse:

<debug>execQuery=SELECT  FROM  LIMIT 0,106</debug>

 

SO... in an attempt to make my query fit what Rico is expecting, I modified as follows:

SELECT * FROM (SELECT 'e' AS TableName,eval_id AS id,discipline_id AS discipline FROM tbl_evaluation AS a)
UNION ALL (SELECT 'p' AS TableName,pocid AS id,discipline_id AS discipline FROM tbl_poc AS b)
UNION ALL (SELECT 'd' AS TableName,dcid AS id,discipline_id AS discipline FROM tbl_dc AS c)
UNION ALL (SELECT 't' AS TableName,tp2id AS id,discipline_id AS discipline FROM tbl_tp2 AS f)

Running the above query at the command line results in the following error:

ERROR 1248 (42000): Every derived table must have its own alias

 

I have searched this forum and Google and cannot figure out what I am doing wrong. I have tried various combinations of parenthesese and aliases to no avail.

I guess my actual question is: Should I find a way to modify the ricoXMLResponse script (I am not very confident in my abilities in that regard) OR what am I missing on the query?

 

Thank you in advance for the help.

 

matt

 

[Mchl]

Did you try this one?

SELECT * FROM (SELECT 'e' AS TableName,eval_id AS id,discipline_id AS discipline FROM tbl_evaluation) AS a
UNION ALL (SELECT 'p' AS TableName,pocid AS id,discipline_id AS discipline FROM tbl_poc)
UNION ALL (SELECT 'd' AS TableName,dcid AS id,discipline_id AS discipline FROM tbl_dc)
UNION ALL (SELECT 't' AS TableName,tp2id AS id,discipline_id AS discipline FROM tbl_tp2)

[mellis95]

Wow. That did it. I think I tried every variation BUT that one. I was trying to assign an alias after each SELECT. Thank you for the help.

 

Matt

[Mchl]

Yeah, it IS a bit confusing.

Think of this way: UNION creates one large table from four smaller ones, so it only need one alias, which you specify for first table in UNION.