Professional Web Applications Themes

help with SQL SELECT statement - Dreamweaver AppDev

Hi, I'm working with MS SQL 2000 and my page is an ASP VBscript page. I am having trouble with a SELECT statement. basically I have 1 table: SAMPLEtable (4 columns) ID (int) response (text) groupid (int) setid (int) Sample data 1 response1 15 3 2 response2 15 3 3 response3 15 3 4 response4 15 1 5 response5 15 1 6 response6 15 1 7 response7 14 4 8 response8 14 4 9 response9 14 4 So what I would like to do is make a SELECT statement where I could say SELECT all WHERE groupid = 15, but ...

  1. #1

    Default help with SQL SELECT statement

    Hi,
    I'm working with MS SQL 2000 and my page is an ASP VBscript page.

    I am having trouble with a SELECT statement.

    basically I have 1 table:

    SAMPLEtable (4 columns)

    ID (int)
    response (text)
    groupid (int)
    setid (int)

    Sample data
    1 response1 15 3
    2 response2 15 3
    3 response3 15 3
    4 response4 15 1
    5 response5 15 1
    6 response6 15 1
    7 response7 14 4
    8 response8 14 4
    9 response9 14 4


    So what I would like to do is make a SELECT statement where I could say
    SELECT all WHERE groupid = 15, but then I would like it to be able to then
    group the responses by placing all of the records where the "setid" are
    the same into one record. So the output I would like to get is the
    following:

    record1 response1 response2 response3 15 3
    record2 response4 response5 response6 15 1

    so only 2 records in total would be given, but it actually combined the
    data of 6. Does anyone know if this is possible? Or if not, where I
    could look for help?

    Thanks!
    Tim
    TimNMtl Guest

  2. #2

    Default Re: help with SQL SELECT statement

    If you are just trying to format the output on the page, that's not really a
    job for a select statement. Do the formatting with your asp code. I'm not an
    asp person, so I can't help you out with out.

    mxstu Guest

  3. #3

    Default Re: help with SQL SELECT statement

    Okay.. I was wondering if this part of the format can be done from the
    SELECT statement to begin with... If the data is grouped this way than it
    would make the formatting I do later on the page much more managable, but
    maybe it can't be done with a SELECT statement?


    On 8/20/05 8:56 PM, in article de8jg4$i4j$macromedia.com, "mxstu"
    <com> wrote:
     

    Timothy Guest

  4. #4

    Default Re: help with SQL SELECT statement

    As you said, you use the WHERE clause to filter the records:

    WHERE groupid = 15

    Then use an ORDER BY clause to order the results the way you want. Something
    like

    ORDER BY setID, response

    Then in your output just create a new row whenever the "setID" changes.

    mxstu Guest

  5. #5

    Default Re: help with SQL SELECT statement

    >but maybe it can't be done with a SELECT statement?

    I am not saying that this could not be done in a sql statement, simply that it
    IMO it "should not" be done. Formatting the output is ASP's job. The
    database's job is to retrieve the data.

    However, even if you could do this, you would have no way of knowing how many
    "response" columns would be generated.... and I think the query performance
    would be less than ideal.

    mxstu Guest

  6. #6

    Default Re: help with SQL SELECT statement

    Okay.. I will look into formatting it in the ASP. The main reason I was
    hoping to do it in the SQL is that it's actually more complex than that. I
    am joining information from 4 tables in my SELECT statement and the part is
    actually a sub-part of it.

    Basically my select statement takes user information from one table and
    matches it with their company information from another table then grabs
    questions from a questionnaire table and matches them with the responses for
    a fourth table which answers match those of each user. And it is generating
    all the appropriate responses for each particular user that I was trying to
    figure out with my previous question. I'll try to do it in my ASP
    formatting.

    Thanks
    T


    On 8/20/05 9:44 PM, in article de8mac$l7q$macromedia.com, "mxstu"
    <com> wrote:
     

    Timothy Guest

  7. #7

    Default Re: help with SQL SELECT statement

    So are you trying to create/display a questionaire for a single user to fill
    out ... ?




    --------------------------------------------------------------------------------
    ---------------
    | Question | Response1 | Response2 | Response 3 | Response4 |

    --------------------------------------------------------------------------------
    ---------------
    | 1) Does...| option 1 | option 2 | option 3 | option 4 |

    --------------------------------------------------------------------------------
    ---------------
    | 2) What... | option 1 | option 2 | option 3 | option 4 |

    --------------------------------------------------------------------------------
    ---------------
    | 3) Are ... | option 1 | option 2 | option 3 | option 4 |

    --------------------------------------------------------------------------------
    ---------------
    | 4) Which..| option 1 | option 2 | option 3 | option 4 |

    --------------------------------------------------------------------------------
    ---------------

    mxstu Guest

  8. #8

    Default Re: help with SQL SELECT statement

    (Posted as a new message because newsgroup users don't seem to see modifications to previous posts)

    .... or display the results of questionaires already filled out by one or more users .... ?
    mxstu Guest

  9. #9

    Default Re: help with SQL SELECT statement

    The second of the two: 

    I have managed to work it out okay using two completely separate recordsets
    and then formatting the data in my ASP, although I was hoping to find a more
    efficient way which would only involve one recordset rather than two.


    On 8/20/05 11:04 PM, in article de8qvi$q6p$macromedia.com, "mxstu"
    <com> wrote:
     

    Timothy Guest

  10. #10

    Default Re: help with SQL SELECT statement

    Well, you're right .... this is bit more complex than the original example. Is
    the formt of the query results an issue because there is only one user response
    to each question, but you need to output a fixed number of columns ...?

    | Quest | User | Resp 1 | Resp 2 | Resp 3
    --------------------------------------------------------------------
    | 1 | UserA | - | X | - |
    --------------------------------------------------------------------
    | 1 | UserB | - | - | X |
    --------------------------------------------------------------------
    | 1 | UserC | X | - | - |


    What is the maximum number of response columns you will have?

    mxstu Guest

  11. #11

    Default Re: help with SQL SELECT statement

    Well the number of response columns depends on each questionaire (there are
    many), but I would say never more than 20. The formated results is really
    just because I think that it would be quicker to have the database generate
    single records that are properly linked rather than having the database
    generate 2 types of records that the users browser has to figure out.


    On 8/21/05 12:21 AM, in article de8vfq$1ov$macromedia.com, "mxstu"
    <com> wrote:
     

    Timothy Guest

  12. #12

    Default Re: help with SQL SELECT statement

    > I think that it would be quicker to have the database generate 

    Without seeing the code, or understanding what your output looks like, it is
    really hard to say anything with certainty. However, how a client browser
    interprets the hmtl code of the output is a completely separate issue than
    database performance or asp code performance on the server as it generates the
    output page. Obviously, browser performance is a concern but it is not the
    only concern. You wouldn't want a page that takes 0.2ms to display in the
    client browser, but takes 29sec alone just to retrieve the information from the
    database, due to the structure of the sql query.

    mxstu Guest

  13. #13

    Default Re: help with SQL SELECT statement

    I don't think it's possible to build the structure of an ADO Recordset on
    the fly like that. What I mean is, what there are three records for setId =
    3 :

    record1 response1 response2 response3 15 3

    but only one record for setId = 1

    record2 response1 15 3

    See what I mean, you have two rows with separate data structures. What if
    there were six records for setId = 4, then you'd have another with 6
    response columns.

    The only way, I can think of, is to restructure your database.

    It appears that a record can have multiple responses. I think this is a
    Question - Response situation? If so, then thats one to many right there,
    and should be two separate tables

    Questions
    Qid (pk)

    Responses
    Rid (pk)
    Qid (fk

    Getting Responses for Questions is obvious in this case.

    Now theres a GroupId as well. So is that "questions make a group of related
    questions". If so then this

    Groups
    GroupId (pk)

    Questions
    Qid (pk)
    GroupId (fk)

    Then the sql would be something like "get all questions and responses for a
    particular group"

    This assumes so far that:

    A Question belongs to one and only one Group
    A Group has multiple Questions
    A Question has multiple Responses
    A Response belongs to one and only one Question

    Now, a Question may have the same Response, but whats the differentiator?
    Like, ask two people what color is the sky (the Question), and they respond
    "blue", (the Response)

    So,

    Question1 "What color is the sky"

    Response1 "Blue" in response to Question1
    Response2 "Blue" in response to Question1

    querying that - "give me all responses for QId = 1" will get two responses.
    So, is the setId a diferentiator? Maybe the first user gets assigned setId =
    1, the second is setId = 2

    Response1 "Blue" in response to Question1, part of set 1
    Response2 "Blue" in response to Question1, part of set 2

    Lets assume that Group record 1 has this data

    Group1 "Questions about the weather"

    A user selects the Group1 questionairre, and gets assigned set number 1 to
    track his responses. At the end of the questions we pull a full n
    recordset, to look like this (column names on first line)

    GroupName | Question | Response | SetID
    Questions about weather | Color of sky | Blue | 1
    Questions about weather | Hot or Cold | Hot | 1

    Theres repeated data, but thats the nature of the beast (Joins).

    Hope that helps, and in some way relates to your situation.

    Ron

    "TimNMtl" <com> wrote in message
    news:168.15.101... 


    RYoung Guest

Similar Threads

  1. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  2. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  3. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  4. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  5. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139