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+-----------+-------------+------+-----+---------+-------+
| 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+----------------+-------------+------+-----+---------+-------+
| 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 |
+----------------------------+-------------------------+----------------+----------------+
- How do I make this normalized? I mean that for a given ,
surveyID
it should display the& the question could be either from thequestion, response, recepient_name, submitter_name
table or thecustom_questionstable depending on thequestions
, but it should be displayed in the same questionsquestionID
column only. - Is it possible to make a column a foreign key referring
to two different columns in two different tables (i.e., making
the foreign key referring toquestionIDfromCID
table orcustom_questionsfromPID)?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;
Source (Stackoverflow)