为两个表选择SQL Query？

I have two tables

我有两张桌子

Table A : Content Question , its options and correct answer

表A:内容问题,其选项和正确答案

Table B : This table is for time allow to answer the question in given time. this table has Question_Id field which either have question id or zero. zero means if for Table A Question Id is not found in Table B then default Time will be Table B's Question_Id=0 > 5 Min

表B:该表用于在给定时间内回答问题的时间。此表具有Question_Id字段,该字段具有问题ID或零。零意味着如果表A中没有找到问题ID,那么默认时间将是表B的Question_Id = 0> 5 Min

Now I want the data like Result table from query. By using Select Query with Join I am getting question details, which are matched with question Id (1,2,4) means for Question 3,5,6 row not getting that showing in result table.

现在我想从查询中获取像Result表这样的数据。通过使用选择查询与加入我得到问题详细信息,其与问题ID(1,2,4)匹配问题3,5,6行未在结果表中显示。

Please suggest what sql query should write so that I can get result like Result Table's content.

请建议sql查询应该写什么,以便我可以得到像Result Table的内容的结果。

2 个解决方案

#1

I change your tables to small and simple tables and you can see the result in:

我将表更改为小而简单的表,您可以在以下位置看到结果:

SQL Fiddle

or try this query:

或尝试此查询:

SELECT t1.questionid, 
       t1.question, 
       t1.options, 
       t1.answer, 
       COALESCE(t2.timingstatement, '5 Min') TimingStatement 
FROM   tablea t1 
       LEFT OUTER JOIN tableb t2 
                    ON t1.questionid = t2.questionid; 

#2

Try this

SELECT Q.QuestionID,Q.Question,Q.Options,QAnswer,
       CASE WHEN Q.QuestionID NOT IN (SELECT QuestionID FROM Table2) THEN '5 Min'
            ELSE T.TimingStatement
       END [TimingStatement]
FROM Table1 Q
JOIN Table2 T ON Q.QuestionID = T.QuestionID