I have created MySQL query interpolation below in Workbench. The query was so fit to my needs except that I could not effect the "User Defined Variable" @var1/2/3 as my field aliases.
I am getting an error on AS @var1 in this line (SELECT field4 FROM view_table LIMIT 0, 1) AS @var1
which I suspect the field Alias is not accepting "User Defined Variables" as illustrated in my below MySQL query:
DROP VIEW IF EXISTS view_table;
CREATE VIEW view_table AS
SELECT table3.field1, table1.field2
FROM table1
JOIN table2 ON table1.table2_id = table2.id
JOIN table3 ON table1.table3_id = table3.id
JOIN table4 ON item_var.table4_id = table4.id
WHERE table2.id = 1
ORDER BY table1 ASC LIMIT 3;
SET @var1 := (SELECT table3.field1 FROM view_table LIMIT 0, 1); -- created for the @var1 variable
SET @var2 := (SELECT table3.field1 FROM view_table LIMIT 1, 1); -- created for the @var2 variable
SET @var3 := (SELECT table3.field1 FROM view_table LIMIT 2, 1); -- created for the @var3 variable
SELECT table4.field1, table2.field2, table2.field3,
(SELECT field4 FROM view_table LIMIT 0, 1) AS @var1, -- @var1 is not recognized as alias
(SELECT field5 FROM view_table LIMIT 1, 1) AS @var2, -- @var2 is not recognized as alias
(SELECT field6 FROM view_table LIMIT 2, 1) AS @var3 -- @var3 is not recognized as alias
FROM table2
JOIN table4 ON table2.id = table4.id
WHERE table2.id = 1;
Here's the query result I want:
+-------------+------------+------------+------------+------------+-------------+------------+
|name |Field1 |Field2 |Field3 |@var1 |@var2 |@var3 |
+-------------+------------+------------+------------+------------+-------------+------------+
|XYZ Company |Field1Data |Field2Data |Field3Data |Field4Data | Field5Data |Field6Data |
+-------------+------------+------------+------------+------------+------------+-------------+
Can anybody help me figure-out the right MySQL syntax for the header variable as field Aliases?
UPDATE: I have posted the solution below for reference.
At last! I got the solution!
I just need to concatenate my last query above to be able to insert the field variables I created. Then, pass it on a statement, execute the statement and deallocate to release it.
SET @statement_var = CONCAT("
SELECT table4.field1, table2.field2, table2.field3,
(SELECT field4 FROM view_table LIMIT 0, 1) AS '", @var1, "' ,
(SELECT field5 FROM view_table LIMIT 1, 1) AS '", @var1, "' ,
(SELECT field6 FROM view_table LIMIT 2, 1) AS '", @var1, "'
FROM table2
JOIN table4 ON item_table2.id = table4.id
WHERE table2.id = 1;");
PREPARE statement FROM @statement_var;
EXECUTE statement;
DEALLOCATE PREPARE statement;
Hope this helps for others with interpolation issue. Cheers!