I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time. I am trying to get an Excel type report out of the DB using SQL Query to combine tables to give me all forms and members connected to said form. Our Member table holds all the customer data but only has one column (UID) that has the same data on other tables with different column names. I have used the query below to fix this mismatch:
SELECT * FROM dbo.Answer a INNER JOIN dbo.tasklist_data tld ON a.source_taskID = tld.TaskID INNER JOIN dbo.Member m ON tld.createdby = m.uid INNER JOIN dbo.Form f ON a.FormID = f.FormID INNER JOIN dbo.Element_Answer ea ON a.ID = ea.AnswerID INNER JOIN dbo.Form_Elements fe ON ea.ElementID = fe.ElementID
The problem I am having is one table (Element_Answer) has a column called Element_answer that holds the data of what was picked when the form was filled out. The data in this column is mainly integers ranging from 1 to 9 which link to another table (Form_Element_groups) that has the same number data in a column called sort_order. The ea.Element_Answer column also has text data (
*%text%*75193) that represents another table (Element_answer_text) that holds the actual text that was written on the form. The number next to the
%text% represents the ID on the Element_Answer_text table.
I am trying to combine the two tables(ea and feg). When I try to INNER JOIN tables Element_Answer to Form_Element_Groups using the ea.Element_Answer and feg.Sort_order columns as the common, it gives me the above error.
My desired output would be to be able to combine these two tables, so that I can see all my data, possibly ignoring all the
%test% entries or possibly having the actual text from the Element_answer_text table replace the
I apologize in advance if I gave too much information. The tables in question are the Element_Answer table, which holds 5 columns (ID, AnswerID, ElementID, Element_answer, and status). The Element_answer column is important because it holds the integer data that I need that links to the other table called Form_Element_Groups that has 7 columns (ID, elementID, option_span, sort_order, group_title, group_text, and category). The sort_order column is the column I am trying to match up with the element_answer column because the data that matches up on these two columns will give me the desired result of only what I want to see from the feg table's group_title column. The ea.Element_answer column also has text entries that link to the Element_Answer_Text table that has two columns (ID, and Element_Answer_text). The eat.element_answer_text column holds the actual text.
If I can get a query that ignores the text in the ea.Element_answer column and only gives me my integer data that matches up, that would be great. If I can get a query that can not only give me my integer data that matches, but the actual text from the Element_Answer_text table, that would be even greater.
I can't figure out how to match the text data in the ea.Element_answer column with the actual text located on eat.Element_answer_text table/column because it has this weird
*%text%*4 data within the ea.Element_answer column referring to the ID on the eat table. (I just picked
4 as an example, there are many other
*%text% entries in that ea.Element_answer column that start with
*%text%*4 and end with