Creating Query with Sub-Query containing multipal records

Question

Post reply

Creating Query with Sub-Query containing multipal records

netguykb

Ten Centuries

Points: 1310
More actions
August 9, 2009 at 9:50 pm

#137916

I have created a simple query which includes a sub-query containing multiple records (Dah ... right away I have problems). The SQL Output is intended to display as HTML [options] tag in a Select Field. The idea is to display the record according to the ID but also list out additional [options] for each of the sub-query results. Here is what I have so far.
NOTE: did not use these tags <> for the post istead used [] for html
SELECT '[option value="' + CONVERT(varchar(10),EVENT_TYPE_ID) + '"]' + TYPE_NAME + '[/option]' AS [CURRENT_SELECT],
(SELECT '[option value="' + CONVERT(varchar(10),EVENT_TYPE_ID) + '"]' + TYPE_NAME + '[/option' AS [OTHER_OPTIONS]
FROM REF_EVENT_TYPE
WHERE DISPLAY_STATUS = 'ACTIVE' AND NOT EVENT_TYPE_ID = 2)
FROM REF_EVENT_TYPE
WHERE EVENT_TYPE_ID = 1
Ideally the result intended is as follows:
[option value="1"]Value From Parent SELECT[/option]
[option value="2"]Record 2 From Sub-Query[/option]
[option value="3"]Record 3 From Sub-Query[/option]
[option value="4"]Record 4 From Sub-Query[/option]
etc...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I'm not sure I understand what you want to do.

I suggest you post your tables definition, some sample data, what you have coded so far and the desired output.

See this article for more info:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Try moving the subquery to the from clause as a derived table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kupy SSCommitted Points: 1652 More actions · Answer 3

Hi,

Did you try useing union?

SELECT '[option value="' + CONVERT(varchar(10),EVENT_TYPE_ID) + '"]' + TYPE_NAME + '[/option]' AS [CURRENT_SELECT]

FROM REF_EVENT_TYPE

WHERE EVENT_TYPE_ID = 1

union all

SELECT '[option value="' + CONVERT(varchar(10),EVENT_TYPE_ID) + '"]' + TYPE_NAME + '[/option' AS [OTHER_OPTIONS]

FROM REF_EVENT_TYPE

WHERE DISPLAY_STATUS = 'ACTIVE' AND NOT EVENT_TYPE_ID = 2