Solved

Sql Syntax

Posted on 2009-05-11
4
208 Views
Last Modified: 2013-12-07
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
Comment
Question by:newbie27
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 200 total points
ID: 24356434
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
 
LVL 25

Expert Comment

by:reb73
ID: 24356601
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
 
LVL 8

Author Comment

by:newbie27
ID: 24356825
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
 
LVL 25

Accepted Solution

by:
reb73 earned 300 total points
ID: 24356874
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now