0

I am not more familiar with MySQL.i have been working in mssql. currently, we have a requirement to select table column fields replaced by values from multiple tables. I tried out with some queries but not getting the proper result since it has some inner options. please refer to the fiddle. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=15c6f66c363ab16a3437a3911c266bde

Please advise me on this

Thanks in advance

EDIT: The code (and text) from dbfille:

1:

CREATE TABLE TableData_400
 (id INT,
 created_date DATETIME,
 sample_1 TEXT,
 sample_2 TEXT,
 sample_3 TEXT,
 sample_4_1 TINYINT,
 sample_4_2 TINYINT,
 sample_4_3 TINYINT                 
 );
INSERT INTO TableData_400 VALUES
('1','2019-05-17 06:19:13','aaa', 'bbb','ccc',0,0,1);

select * from TableData_400

2:

DROP table TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id INT,
 sample_title TEXT               
 );

3:

INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4','checkbox');

select * from TableData_elements where Dataid=400

4:

CREATE TABLE TableData_elements_options
 (Dataid INT,
 sample_id INT,
 option_id INT,
 optionss TEXT               
                   );
 INSERT INTO TableData_elements_options VALUES
('400',4,1,'First'),
('400',4,2,'second'),
('400',4,3,'Third');

select * from TableData_elements_options where Dataid=400

expected result like

select datas from TableData_400 like the following way ,

 id  created_date            fname Sname Lname first second third 
 1    2019-05-17 06:19:13    aaa    bbb   ccc   0     0       1

5:

SELECT  CONCAT( 'SELECT ', GROUP_CONCAT(CONCAT('sample_', sample_id, ' ', sample_title)),
               ' FROM TableData_400'
               )
INTO @sql FROM TableData_elements;
SELECT @sql; 

PREPARE myStatement FROM @sql;
EXECUTE myStatement ;
DROP PREPARE myStatement;
0

You could change sample_id, like below:

DROP TABLE TableData_elements;
CREATE TABLE TableData_elements
 (Dataid INT,
 sample_id CHAR(10),
 sample_title TEXT               
 );
INSERT INTO TableData_elements VALUES
('400','1','Fname'),
('400','2','Sname'),
('400','3','Lname'),
('400','4_1','first'),
('400','4_2','second'),
('400','4_3','third');

OR you could change query at the end:

SELECT  
  CONCAT( 'SELECT ', 
          GROUP_CONCAT(CONCAT('sample_', 
                              te.sample_id, 
                              CASE WHEN teo.option_id>0 THEN 
                                    CONCAT('_',teo.option_id) 
                                    ELSE '' END, 
                              ' ', 
                              sample_title)),
          ' FROM TableData_400' ) 
FROM TableData_elements te 
LEFT JOIN TableData_elements_options teo ON 
        teo.Dataid=te.Dataid and 
        teo.sample_id=te.sample_id ;

EDIT: (also tak not of optionss):

SELECT  
  CONCAT( 'SELECT ', 
          GROUP_CONCAT(CONCAT('sample_', 
                              te.sample_id, 
                              CASE WHEN teo.option_id>0 THEN 
                                    CONCAT('_',teo.option_id) 
                                    ELSE '' END, 
                              ' ', 
                              CASE WHEN teo.option_id>0 THEN
                                 teo.optionss
                              ELSE sample_title END)),
          ' FROM TableData_400' ) 
FROM TableData_elements te 
LEFT JOIN TableData_elements_options teo ON 
        teo.Dataid=te.Dataid and 
        teo.sample_id=te.sample_id ;
| improve this answer | |
  • thankyou for your reply. i tried this query but i have return the result for option field is option for all 3 options, we can only take the fields dynamically so we can not do any alias manualy. – Muhasseena T U Feb 11 at 11:15
  • and we can not do any changes to table fields, because it's dynamically generated. need to take options from TableData_elements_options table dynamically. – Muhasseena T U Feb 11 at 11:24
  • Thank you for your reply. I want to do some more customization on this (append single quotes for field names). dbfiddle.uk/… – Muhasseena T U Feb 25 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.