Hi everyone, This was originally posted in the CFML-general channel. I while I realise that cross-posting is frowned upon - I didn't realise there was a SQL specific channel - when I first posted : So I thought it best - if this discussion actually took place here. --- This isn't strictly a CFML question... It might be "JUST" an SQL problem - but since I can't solve it for myself in JUST SQL, I'm open to solutions that may need some massaging in CFML, to get it working. Basically I need to create a join between two tables, where one side of the join is a column name, not a value in a column. I have created a dbFiddle to better explain. Which I thought - made it clear.... but from a few of the responses I already got - perhaps not... So let me try again... I am using MariaDB 10.2.44 There is no chance of upgrading the DB or changing to an other flavour. MariaDB (10.2) is a drop-in replacement for MySQL 5.7 Think of a survey. I have a "Questions" table. And I have an "Answers" table. The answers table in the dbFiddle is called

vxml_xxxxxx

The questions table in the dbFiddle is called

ws_question

At the moment, I have already working SQL, that creates a data export. This SQL is dynamically created via 3 CFML functions, that call each other and themselves recursively, to produce the SQL string. (It doesn't run the SQL - just gives the SELECT...as a string.) I have been asked to include the question's TEXT to the existing report - as an "extra" report. Subsequently, I don't want to create a new SQL for this extra report. The dynamic SQL is a real "dragons live in here - never touch it - don't even think of touching it" masterpiece of complexity. I am exaggerating (a little) but as way of an analogy; If there was an existing function in CFML to manipulate a string, exactly the way you wanted - let's ignore that. Create our own version - but ensure that our version is actually 5 functions of "weird" to get the EXACT same end result that a built-in-function (that has ALWAYS existed) would have given you. Then throw that against a wall, let it slide down to the floor, then reconstruct it... call it recursively, until such time that you have nothing left to throw at the wall.... Add salt : Enjoy. (Seriously - the 3 functions combined are about 2500 lines of code that I didn't write and so trying to fit that amount of code into my brain to understand what it is all doing - well - it just gives me a rash! Anyway : Just to keep it simple and match the dbFiddle; The dynamic SQL produces the following columns of data:

ID, SURVEY_ID, PvFf9Bzd_response, yW3AEXOT_response, IES35zCj_response, IES35zCj_response_reason

with an output that looks like;

1, 101, 1,1,1,'1: dog is always better'

This dynamic SQL (again to keep in-sync with the fiddle) that produces this is;

select * from vxml_xxxxxx;

What I need to produce is below - where the difference is I need to insert before each "answer" column - another column that will hold the text of the question, that the answer is for.

ID, SURVEY_ID, PvFf9Bzd_text, PvFf9Bzd_response, yW3AEXOT_text, yW3AEXOT_response, IES35zCj_text, IES35zCj_response, IES35zCj_reason_text, IES35zCj_response_reason

with an output that looks like;

1, 101, On a scale from 1 to 5-do you like dogs?, 1, How happy is a dog to see you?, 1, How happy is a cat?, 1, A dog is always better-explain,1: dog is always better

Because I already have the SQL as a "string" - I am planning on just doing some REGEX / string manipulation to add the extra

questionText

columns and a join. The text below and the dbFiddle - just show me adding in ONE column. I know I need multiple columns : 1 for each answer column. I plan to just dynamically generate these.

select 
    v.*
-- , wsq.questionText
from vxml_xxxxxx v
-- inner join ws_question wsq on xxxxxxxxxxxxxxxxxxxxxxxxx
;

The trouble I have is producing a working JOIN. Although the data I need IS in the

ws_question

table AND

ws_question

is a normalised table, the "real" schema of the

vxml_xxxxxx

table is just like the dynamic SQL produces. In that : it is NOT normalised. There is no FK to the

ws_question

table. The

vxml_xxxxxx

table is generated at the time a new questionnaire is deployed. It uses the

internalName

of the question in the

ws_question

table to create the columnName in the

vxml_xxxxxx

table. (that stores the answers) Because there is no FK, I can't "look up / reference" (or at least I don't know how to) get the column name,

PvFf9Bzd_response

as a "value" so that I can use it in the JOIN . I am not "stuck" on using a

join

either. I don't care, at all, what the final SQL looks like. I initially thought of creating a many-to-many - but "I" still need a way to "map" one to the other - to create the m2m, in the first instance. I would then just ADD to the m2m by amending the code that creates the

vxml_xxxxxx

table. (Once "deployed" - a survey cannot be altered - so the m2m work ONLY needs to be when the

vxml_xxxxxx

is created.) I don't care what the solution looks like - I just need one. As an example - if the SQL required ended up producing 20 intermediary columns - to "get to" the questionText column - I am Ok with that. I can wrap the final SQL in (select column1, column2, etc from ( THE REAL SQL HERE) )subq before it is executed. I don't mind if I need to create a stored procedure either. I'd "rather" not - but if that gets me a working solution, so be it. Really the point I am making is - I am open to ANY working solution. Lastly - (I don't think) I need anyone to actually produce the code / SQL I won't say "no" - of course... As I rewrite this, now - just thinking aloud : In my mind - I am just hoping for the steps / logic for the "mapping". and then I'll create a m2m for existing surveys/questions/answers And going forward use that m2m for the 2nd extract. If anything "extra" (Meta-data / whatever) ever needs to be added that can be included, via an "extra info" table to the m2m. As always - thanks in advance for any ideas you might have.

If you are just looking for a builder i would do something like this and pass in the tableName this has not been written but you are basically just building your query dynamically and without any parameterization i wouldn't trust it with any input 🙂

<cfscript>
    function createSQL(tableName)
    {
        var cols = QueryExecute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA_COLUMNS WHERE TABLE_NAME = '#tableName#' and COLUMN_NAME like '%_response%'");
        var columns = ListToArray(ValueList(cols.COLUMN_NAME));
        var begSelect = ["SELECT ID, SURVEY_ID, "];
        var begFrom = ["FROM #tableName#"];
        var begJoin = [];
        var i = 1;
        for ( var column in columns )
        {
            ArrayAppend(begSelect, "col_#i#.questionText as #column#_text, #column#, ");
            ArrayAppend(begJoin, " INNER JOIN ws_questions col_#i# on col_#i#.internalName = '#column#'
            i++;
        }
        ArrayAppend(begSelect, "'lastColumn' as last");
        return ArrayToList(begSelect, " ") & ArrayToList(begFrom, " ") & ArrayToList(begJoin, " ");
    }

</cfscript>

select

*

from vxml_xxxxxx v

join (select

max(case when internalName = 'PvFf9Bzd_response' then questionText else null end) as PvFf9Bzd_text,

max(case when internalName = 'yW3AEXOT_response' then questionText else null end) as yW3AEXOT_text,

max(case when internalName = 'IES35zCj_response' then questionText else null end) as IES35zCj_text,

max(case when internalName = 'PvFf9Bzd_response' then questionText else null end) as IES35zCj_reason_text

from ws_question) wsq on 1=1

;

Thanks everyone, I started with what was mentioned here and in the original thread. I am not sure why I didn't think of it myself... but once I realised that I could write a whole

SELECT

statement, per column for the join... I went with

coalesce

COALESCE( (SELECT wsq.questionText FROM ... WHERE ... ), (NULL) ) AS zzzzz

Thanks again, for all your help! I really appreciate it!