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

Question

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    |

+----------------------------+-------------------------+----------------+----------------+

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.

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 1

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;