[Pwn]

The following SQL doesn't work as I suspected it would

<code>
select f.name, d.name, b.abbr, b.question, (select validanswer.answer where validanswer.quest_id = b.quest_id and validanswer.is_inactive = 0), d.name 
from questlist as a 
inner join quest as b on a.quest_id = b.quest_id 
inner join pro as c on a.owner_id = c.pro_id 
inner join proname as d on c.proname_id = d.proname_id 
inner join resunit as e on c.resunit_id = e.resunit_id 
inner join loc as f on e.
<\code>

I was hoping the nested select after b.question would return a list of results, as an element, but, of course, it didn't. Does anybody have any suggestions on how to get this to work?

Also, apparently the code tags no longer work, try phpbb style and html style, no tagging.

This post has been edited by modi123_1: 09 November 2012 - 08:19 AM
Reason for edit:: highlight the text and just click the code button

[baavgai]

Putting the select as a field is possible in some SQL implementations, but it's generally a bad idea. Keep in mind that the select with get executed for every result.

The requirement is that it only return one value. If it returns more than than, you're going down.

Your SQL has extra issues in that is't missing a from.

Assuming we have a from, consider this instead:

select quest_id, min(answer) as answer
	from validanswer
	where is_inactive = 0
	group by quest_id

This will get us only one row per quest_id. You don't have to do this is your relationship contraints would forbid it, but this will ensure it.

Now, we can put that in your query:

select f.name, d.name, b.abbr, b.question,
		va.answer,
		d.name
	from questlist as a 
		inner join quest as b 
			on a.quest_id = b.quest_id 
		inner join pro as c
			on a.owner_id = c.pro_id 
		inner join proname as d
			on c.proname_id = d.proname_id 
		inner join resunit as e 
			on c.resunit_id = e.resunit_id 
		inner join loc as f 
			on e.-- oops, you need more here
		left outer join (
			select quest_id, min(answer) as answer
				from validanswer
				where is_inactive = 0
				group by quest_id
			) va
				on va.quest_id = b.quest_id

Hope this helps.

[Pwn]

I figured baavgai would have an answer, however, what I'm trying to accomplish it creating a Excel file, with all possible answers to each question in one cell, in list format. First step is to get the answers in one line; I don't know if a group by would do the trick, either that or I'm not using group by correctly. I'm more used to using Crystal grouping and can't seem to get grouping to work in SQL. Also, Crystal has a subreport function, which is basically what I'm trying to accomplish in SQL.

If I can't do it with sql (or not reliably), then I'm going to have to touch the results in vba and compress the list.

Thanks for the help baavgai!

[baavgai]

SQL deals with data points. Period. The minute you starting thinking like a report writer, and want to screw with concatenation, strings, etc, you're in trouble. SQL gets the result set, some other tool, like crystal reports, takes a result set and makes it pretty.

In SQL Server, you can try the COALESCE function. There is a hack that can get you concatenated data: http://www.mssqltips...-in-sql-server/

Temp tables and procedural stuff are also an option.

[e_i_pi]

In regards to getting all the results in one line, this isn't something that SQL is designed for. As baavgai stated, SQL returns data points, not collated data.

Mind you, if you are using MSSQL, then it is possible to return the data in XML format by appending the command FOR XML at the end of your query, and supplying and XML mode. If this is helpful, I'd suggest reading up on the [http://msdn.microsoft.com/en-us/library/ms178107.aspx]MSDN article on the topic[/url], as the modes produce substantially different output.