CODEWITHSUNDEEP
androidmysql
zyonneo
zyonneo

Reputation: 1389

How to get values from two tables in which one column in each table contains similar value?

I have two tables the queries are given below

db.execSQL("CREATE TABLE IF NOT EXISTS allquestions (num_id INTEGER PRIMARY KEY NOT NULL, questions TEXT NOT NULL,catogery TEXT NOT NULL,age INT NOT NULL)" );
db.execSQL("CREATE TABLE IF NOT EXISTS answers (num_id INTEGER PRIMARY KEY NOT NULL,questionid INT NOT NULL,answer TEXT NOT NULL)" );

enter image description here

Here the num_id in allqestions contains the question number and the answers are there in the table(answers) column answer.In the answer column the questionid contains the question number for the answer.So what I want is how to write the query to get questions from allquestion and its answers from table answer.

Upvotes: 0

Views: 50

Answers (3)

Ullas
Ullas

Reputation: 11556

Try the following. I hope you are looking for the same.
Give a row_number for each group, then use a CASE statement.

Query

select t1.questionid,a.questions,
max(case when t1.row_number=1 then t1.answer else 0 end) as answer1,
max(case when t1.row_number=2 then t1.answer else 0 end) as answer2,
max(case when t1.row_number=3 then t1.answer else 0 end) as answer3
from allquestions a
join (
SELECT @row_number:=CASE WHEN @questionid=questionid 
THEN @row_number+1 
ELSE 1 
END AS row_number,
@questionid:=questionid AS questionid,answer
FROM answers, 
(SELECT @row_number:=0,@questionid:='') AS t
ORDER BY questionid
)t1
on a.num_id=t1.questionid
group by t1.questionid;

Fiddle demo

Upvotes: 0

Abdul Muheet
Abdul Muheet

Reputation: 315

You have to apply the join on both tables.

       SELECT 
      allquestions.`num_id`,allquestions.`questions`,answers.questionid,
       answers.answer from allquestions  JOIN answers ON allquestions.`num_id`
         = answers.questionid

Upvotes: 0

manoj
manoj

Reputation: 118

I think you need it.

select questions,answers from questions que,answers ans where que.num_id=ans.questionid;

Upvotes: 1

Related Questions