5

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

Airful
  • 312
  • 2
  • 12
  • Technically, you need to use Dynamic SQL, which means MySQL's Prepared Statement syntax – Jivan Jul 30 '13 at 09:28

3 Answers3

7

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.

Martin
  • 16,093
  • 1
  • 29
  • 48
  • 1
    currently i have two tables. education, games. but in future there will be more table. so i cann't add join for each table. – Airful Jul 30 '13 at 09:24
  • You would have to redesign the table structure... e.g. survey_data: survey_type_id, col1, col2, col3, col4, col5, col6. survey_type: id, type_name. survey: id, survey_type_id, survey_data_id, date_created. – AEQ Feb 17 '15 at 09:52
  • @AEQ Could you explain that approach you are suggesting some more? – antoniovassell May 26 '15 at 10:18
  • It has been awhile since I had a look at this but I think this is what I was thinking of @antoniovassell: http://sqlfiddle.com/#!2/76889/3 – AEQ May 27 '15 at 09:57
1

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

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
  • Thanks. But is there any solution where i don't have to pass the table name(which is `source_type`)? I just want to pass the `survey` table `id`. The table will be dynamically join with the `survey` from the `source_type`. – Airful Jul 31 '13 at 04:04
  • 1
    How the system will know that you need to join these all tables unless you told the system to join. ?. An alternative solution is already pointed by Martin Parkin, where you need to tell all the tables to go and join. Hope you got my point. – Praveen Prasannan Jul 31 '13 at 04:13
  • The `source_type` column is the table name. If i had the id then i can find the table form the `source_type` column. – Airful Jul 31 '13 at 11:45
0

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

Community
  • 1
  • 1
AEQ
  • 1,339
  • 1
  • 16
  • 20