Ashwath Ashwath - 3 months ago 6
MySQL Question

How to normalize result of sql query and how to refer a foreign key to two columns

I have the following tables:

`person` table

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| person_id | int(100) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+

+-----------+---------------+
| person_id | name |
+-----------+---------------+
| 1 | linon jacob |
| 2 | andrew simons |
| 3 | john random |
| 4 | kayne ran |
+-----------+---------------+

questions table:

+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| QID | int(50) | NO | PRI | NULL | |
| questions | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+

+-----+----------------------------+
| QID | questions |
+-----+----------------------------+
| 1 | how do you like your team? |
| 2 | rate your work load? |
| 3 | rate your work manager? |
+-----+----------------------------+


custom_questions
table:

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| CID | int(20) | NO | PRI | NULL | |
| questions | varchar(50) | NO | | NULL | |
| PID | int(20) | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+

+-----+-------------------------+------+
| CID | questions | PID |
+-----+-------------------------+------+
| 6 | how do you like java? | 1 |
| 7 | how do you like python? | 1 |
| 8 | how do you like python? | 2 |
| 9 | how do you like ruby? | 2 |
+-----+-------------------------+------+


feedback_system
table:

+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| surveyID | int(20) | NO | PRI | NULL | |
| questionID | int(20) | NO | PRI | NULL | |
| recepient_name | varchar(50) | YES | | NULL | |
| submitter_name | varchar(50) | YES | | NULL | |
| response | varchar(50) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+

+----------+------------+----------------+----------------+-------------+
| surveyID | questionID | recepient_name | submitter_name | response |
+----------+------------+----------------+----------------+-------------+
| 1 | 1 | linon jacob | kayne ran | good |
| 1 | 2 | linon jacob | kayne ran | ok |
| 1 | 6 | linon jacob | kayne ran | outstanding |
| 2 | 1 | linon jacob | john random | ok |
| 2 | 6 | linon jacob | john random | outstanding |
+----------+------------+----------------+----------------+-------------+


I'm trying to do the following:

select questions.questions, custom_questions.questions,
feedback_system.recepient_name, feedback_system.submitter_name
from questions, custom_questions, feedback_system
where questions.QID = feedback_system.questionID
or custom_questions.CID = feedback_system.questionID and surveyID = 1;


but it displays the output as:

+----------------------------+-------------------------+----------------+----------------+
| questions | questions | recepient_name | submitter_name |
+----------------------------+-------------------------+----------------+----------------+
| how do you like your team? | how do you like java? | linon jacob | kayne ran |
| rate your work load? | how do you like java? | linon jacob | kayne ran |
| how do you like your team? | how do you like java? | linon jacob | kayne ran |
| rate your work load? | how do you like java? | linon jacob | kayne ran |
| rate your work manager? | how do you like java? | linon jacob | kayne ran |
| how do you like your team? | how do you like java? | linon jacob | john random |
| how do you like your team? | how do you like python? | linon jacob | kayne ran |
| rate your work load? | how do you like python? | linon jacob | kayne ran |
| how do you like your team? | how do you like python? | linon jacob | john random |
| how do you like your team? | how do you like python? | linon jacob | kayne ran |
| rate your work load? | how do you like python? | linon jacob | kayne ran |
| how do you like your team? | how do you like python? | linon jacob | john random |
| how do you like your team? | how do you like ruby? | linon jacob | kayne ran |
| rate your work load? | how do you like ruby? | linon jacob | kayne ran |
| how do you like your team? | how do you like ruby? | linon jacob | john random |
+----------------------------+-------------------------+----------------+----------------+



  1. How do I make this normalized? I mean that for a given
    surveyID
    ,
    it should display the
    question, response, recepient_name, submitter_name
    & the question could be either from the
    custom_questions
    table or the
    questions
    table depending on the
    questionID
    , but it should be displayed in the same questions
    column only.

  2. Is it possible to make a column a foreign key referring
    to two different columns in two different tables (i.e., making
    questionID
    the foreign key referring to
    CID
    from
    custom_questions
    table or
    PID
    from
    questions table
    )?



EDIT:

As per the below comment:

create table person(person_id int(100) primary key not null, name varchar(50) not null);
create table questions(QID int(50) primary key not null, questions varchar(100));
create table custom_questions(CID int(20) not null, questions varchar(50) not null, PID int(20), primary key (CID), foreign key (PID) references person(person_id));
create table feedback_system(surveyID int(20) not null, recepient varchar(50), questionID int(20) not null, submitter name varchar(50), response varchar(10), primary key (surveyID, questionID));


Output should be like:

questions | response | recepient_name | submitter_name |

how do you like your team? | good | linon jacob | kayne ran |
hw do you like your work load? | ok | linon jacob | kayne ran |
how do you like java? | outstanding | linon jacob | kayne ran |

Answer

I believe you need to use some LEFT JOINs, as for each feedback line you EITHER have a question in questions OR you have a question in custom_questions.

Something like this:

SELECT IFNULL(q.questions, cq.questions) as 'question',
   f.recepient_name, 
   f.submitter_name,
   f.response
FROM feedback_system f
LEFT JOIN custom_questions cq
    ON cq.CID = f.questionID    
LEFT JOIN questions q
    ON q.QID = f.questionID 
WHERE f.surveyID = 1;