Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

Sql Syntax

Hello Experts,

I am trying to work on the query to pull 4 options against one question record from the 3 tables.

Please can someone advise how to get this right?

Please have the table and the SQL query attached.

Thanks
S
select mytab.*,lto.[option]  from dbo.LearnItOptions as lto
 
inner join dbo.TestPaperQuestionsOrder as tpq on
 
lto.questionid=tpq.questionid and lto.testid=tpq.testid
 
right outer join  
(select tpq.sortorder,ltq.QuestionContent,ltq.questionid from dbo.LearnItQuestions as ltq
inner join dbo.TestPaperQuestionsOrder as tpq on
ltq.questionid=tpq.questionid and
ltq.testid=tpq.testid
 and  tpq.giftcodeid= 'CHOCOH2UV6V78S'
) as mytab on
 lto.questionid=mytab.questionid
 
 
 
where  tpq.giftcodeid= 'CHOCOH2UV6V78S'
order by tpq.sortorder
 
something like this 
sortorder questioncontent					questid options
===============================================================================
sortorder questioncontent					questid options
===============================================================================
1	  Chocolate was first discovered over 2000 years ago by:	1	The native people of Spain
 									 	The British Colonials 
 										The native people of Central and South America
 										The native Australians 
 
 
2	 Chocolate was first consumed as:				1	Chocolate bars
2	   									A chocolate drink
2	   								 	Chocolate eggs
2	  									Chocolate cake1
 
 
All I am getting now is this
 
1	1	Chocolate was first discovered over 2000 years ago by:	1	The native people of Spain
1	1	Chocolate was first discovered over 2000 years ago by:	1	The British Colonials 
1	1	Chocolate was first discovered over 2000 years ago by:	1	The native people of Central and South America
1	1	Chocolate was first discovered over 2000 years ago by:	1	The native Australians 
2	2	Chocolate was first consumed as:	2	Chocolate bars
2	2	Chocolate was first consumed as:	2	A chocolate drink
2	2	Chocolate was first consumed as:	2	Chocolate eggs
2	2	Chocolate was first consumed as:	2	Chocolate cake

Open in new window

0
newbie27
Asked:
newbie27
  • 2
2 Solutions
 
UnifiedISCommented:
It looks like you have pulled your data correctly but you need to format it so the question is not repeated for each potential answer.  This will be easy to do in reporting software but not as easy in SQL.  If you use Crystal reports, you'd create a group for your question and put your potential answers in the details section.  
0
 
reb73Commented:
You could possibly use the FOR XMLPATH to concatenate option values for a question id in a single row but the formatting may not be perfect - though you could get it to format properly if you have the options as the first column..

See template code below -
select	options = replace((select lto.[option] + ';' from dbo.LearnItOptions as lto where lto.questionid = mytab.questionid FOR XML PATH ('')),';',CHAR(13) + CHAR(10)) 
	,mytab.*
from
	(select tpq.sortorder,ltq.QuestionContent,ltq.questionid from dbo.LearnItQuestions as ltq
	 inner join dbo.TestPaperQuestionsOrder as tpq on
	 ltq.questionid=tpq.questionid and
	 ltq.testid=tpq.testid
	 and  tpq.giftcodeid= 'CHOCOH2UV6V78S'
	) as mytab
inner join
	dbo.TestPaperQuestionsOrder as tpq
	on lto.questionid=tpq.questionid and lto.testid=tpq.testid
where
	tpq.giftcodeid= 'CHOCOH2UV6V78S'
order by
	tpq.sortorder

Open in new window

0
 
newbie27Author Commented:
hello reb73

thanks for the query

have tried as suggested however it gave me the following error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "lto.questionid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "lto.testid" could not be bound.

Open in new window

0
 
reb73Commented:
A small mistake in my original post.. Try -
select  options = replace((select lto.[option] + ';' from dbo.LearnItOptions as lto
			   where lto.questionid = ltq.questionid and lto.testid = ltq.testid
			   FOR XML PATH ('')),';',CHAR(13) + CHAR(10)) 
        ,tpq.sortorder,ltq.QuestionContent,ltq.questionid
from
	dbo.LearnItQuestions as ltq
inner join
	dbo.TestPaperQuestionsOrder as tpq
	on ltq.questionid=tpq.questionid and ltq.testid=tpq.testid
where
        tpq.giftcodeid= 'CHOCOH2UV6V78S'
order by
        tpq.sortorder

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now