select table and replace column fields by value from multiple tables in mysql

Question

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;

Luuk · Accepted Answer · 2020-02-11 11:23:05Z

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 ;

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
– Muhasseena T U, Commented Feb 11, 2020 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
– Muhasseena T U, Commented Feb 11, 2020 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
– Muhasseena T U, Commented Feb 25, 2020 at 8:01

Stack Exchange Network

select table and replace column fields by value from multiple tables in mysql

1 Answer 1

Your Answer

Hot Network Questions

select table and replace column fields by value from multiple tables in mysql

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions