Post reply

Creating Query with Sub-Query containing multipal records

  • 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...

  • August 10, 2009 at 4:21 am

    #1036665

    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://qa.sqlservercentral.com/articles/Best+Practices/61537/

    -- Gianluca Sartori

  • August 10, 2009 at 4:31 am

    #1036670

    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

  • August 10, 2009 at 5:28 am

    #1036689

    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

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

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