CODEWITHSUNDEEP
mysqljoin
Airful
Airful

Reputation: 311

Join table dynamically MySQL

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

Upvotes: 5

Views: 5650

Answers (3)

AEQ
AEQ

Reputation: 1429

You could redesign the table structure which would then allow you to add more survey types, if the columns are the same, then you can reuse columns but if not, null data uses very little space (Ref: NULL in MySQL (Performance & Storage))

SQL fiddle: http://sqlfiddle.com/#!2/76889/3

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

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

Upvotes: 1

Martin
Martin

Reputation: 16423

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.

Upvotes: 7

Related Questions