Join table dynamically MySQL

Question

I am new in MySQL. I am not sure that i am right thing. Need help.

I have a table survey which have the following column:

id, source_type, source_id, survey_date_time.

Other two tables are:

education which have id, col1, col2, col3 columns.

games which have id, col4, col5, col6 columns.

Data in survey table:

id       source_type    source_id    survey_date_time
--------------------------------------------------------
100      education         1         2013-07-25 00:00:00
101      games             1         2013-07-25 00:00:00
102      games             2         2013-07-26 00:00:00
103      education         2         2013-07-26 00:00:00

Data in education table

id    col1          col2          col3      
--------------------------------------------
1     col1_data1    col2_data1    col3_data1
2     col1_data2    col2_data2    col3_data2

Data in games table

id    col4          col5          col6      
--------------------------------------------
1     col4_data1    col5_data1    col6_data1
2     col4_data2    col5_data2    col6_data2

I want to read data dynamically like:

select * from survey left join {survey.sorce_type} on {survey.sorce_type}.id=survey.source_id where survey.id={given_id}

You can find the the schema here

Thanks in advance.

UPDATE : select statement will be survey.*, {survey.sorce_type}.* instead of *

Thanks

Answer 1

This should accomplish what you are looking for:

SELECT *
  FROM survey s
    LEFT JOIN eduction e ON s.source_type = 'education' AND e.id = s.source_id
    LEFT JOIN games g ON s.source_type = 'games' AND g.id = s.source_id

SQL Fiddle is here.

Essentially, this joins the appropriate table based on the source_type. So when it is education it joins to the eduction table (you may have a spelling mistake there) and when it is games it joins to the games table.

Answer 2

For that you need to create a procedure with prepared statements.

DELIMITER |
CREATE PROCEDURE `JoinWithSurvey`(param_leftTable VARCHAR(50), param_id VARCHAR(10))
BEGIN
SET @QUERY1 = concat('select survey.*,',param_leftTable,'.* from survey left join ',param_leftTable,' on  ',param_leftTable,'.id=survey.source_id where survey.id = ', param_id ,';');
 PREPARE stmt FROM @QUERY1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END |

Then call that sp

call JoinWithSurvey('eduction','100');

ID  SOURCE_TYPE SOURCE_ID   SURVEY_DATE_TIME    COL1    COL2    COL3
100 education   1   July, 25 2013 00:00:00+0000 col1_data1  col2_data1  col3_data1

call JoinWithSurvey('games','102');

ID  SOURCE_TYPE SOURCE_ID   SURVEY_DATE_TIME    COL4    COL5    COL6
102 games   2   July, 26 2013 00:00:00+0000 col4_data2  col5_data2  col6_data2
102 education   2   July, 26 2013 00:00:00+0000 col4_data2  col5_data2  col6_data2

I passed id as a varchar here.. You can use as integer type as well.. :)

Have a try

Working fiddle here