Professional Web Applications Themes

SQL query - ASP Database

Mytable has 20 fields.I need select three columns from mytable. My query: ' select columnname1,columnname2,columnname3 from mytable ' Sometimes I don't want make it too long. I write this way 'select * from mytable'. But I don't know if they are the same on performance. r they the same ? Thanks...

  1. #1

    Default sql query

    Mytable has 20 fields.I need select three columns from
    mytable.

    My query:
    ' select columnname1,columnname2,columnname3 from
    mytable '
    Sometimes I don't want make it too long. I write
    this way 'select * from mytable'. But I don't know
    if they are the same on performance. r they the same ?



    Thanks

    Student Guest

  2. Moderated Post

    Default sql query

    Removed by Administrator
    Laurent Lemire Guest
    Moderated Post

  3. #3

    Default SQL Query

    the table called itm_lots contain item_id, and lot_number


    for item no. 1 lot no. A,B,C (A shoud not come again)
    item 2 lot no A,B,C (should come) but should not repeat.

    I NEED QUERY TO AVOICE DUPLICATE IN LOT NO.


    THANKS
    KALYAN

    kalyan Guest

  4. #4

    Default SQL Query

    Hi

    Try this

    select * from itm_lots group by lot_number,item_id

    should work
    kn
    >-----Original Message-----
    >the table called itm_lots contain item_id, and lot_number
    >
    >
    >for item no. 1 lot no. A,B,C (A shoud not come again)
    >item 2 lot no A,B,C (should come) but should not repeat.
    >
    >I NEED QUERY TO AVOICE DUPLICATE IN LOT NO.
    >
    >
    >THANKS
    >KALYAN
    >
    >.
    >
    KANNAN Guest

  5. #5

    Default SQL query

    Hello group,

    How do I define a SQL query that searches for a string in a Memo field in
    Access. Now I have this statement but it doesn't show up with the results I
    would like to see

    SELECT Factuurregel FROM Factuurregels WHERE factuurregel LIKE
    '"&request.QueryString("woningnr")&"'"

    Anybody a solution?

    Thanks in advance

    Greetings RenÚ


    info Guest

  6. #6

    Default Re: SQL query

    q = replace(request.querystring("woningnr"), "'", "''")
    sql = "SELECT Factuurregel FROM Factuurregels " & _
    " WHERE factuurregel LIKE '%" & q & "%'"




    "info" <infowebxp.nl> wrote in message
    news:OilIqrTTDHA.2152TK2MSFTNGP12.phx.gbl...
    > Hello group,
    >
    > How do I define a SQL query that searches for a string in a Memo field in
    > Access. Now I have this statement but it doesn't show up with the results
    I
    > would like to see
    >
    > SELECT Factuurregel FROM Factuurregels WHERE factuurregel LIKE
    > '"&request.QueryString("woningnr")&"'"
    >
    > Anybody a solution?
    >
    > Thanks in advance
    >
    > Greetings RenÚ
    >
    >

    Aaron Bertrand - MVP Guest

  7. #7

    Default SQL query

    For ODBC try: LIKE '%"&request.QueryString("woningnr")&"%'"

    Inside Access: replace the % with *

    Cheers, Daniel
    >-----Original Message-----
    >Hello group,
    >
    >How do I define a SQL query that searches for a string in
    a Memo field in
    >Access. Now I have this statement but it doesn't show up
    with the results I
    >would like to see
    >
    >SELECT Factuurregel FROM Factuurregels WHERE factuurregel
    LIKE
    >'"&request.QueryString("woningnr")&"'"
    >
    >Anybody a solution?
    >
    >Thanks in advance
    >
    >Greetings RenÚ
    >
    >
    >.
    >
    d yates Guest

  8. #8

    Default SQL query

    Hello all.

    I have a tab-delimited text file named "PERSONS" containing a number of
    records with
    -a unique ID
    -first name
    -surname
    -and other information
    for a number of people.

    I also have a FMPro5.5 database "BOOKLOANS" containing fields for
    -unique ID
    -loaned book(s)
    -first name
    -surname

    Now, when I fill in the unique ID in "BOOKLOANS", I want it to send an
    SQL query to "PERSONS" and automatically insert (or show) first name and
    surname in those fields in "BOOKLOANS".

    How do I do this? I'm on a Mac using OS9.2. I've read help files but
    could not make it work.

    ┼ke



    Oke Fireberg Guest

  9. #9

    Default Re: SQL query

    1. Create a relationship between the two databases based on the ID.
    2. In the BOOKLOANS db, paste the first name and surname fields onto the
    layout. Instead of using the BOOKLOANS fields, use the ones from the
    relationship (you'll see the pulldown menu at the top of the "Insert Field"
    dialog box. Instead of using "Current file: PERSONS" use Related file: x"
    where x is the name of the relationship you created.
    3. When you type the id of the user in BOOKLOANS, it will match the id in
    PERSONS and show the user's first and last name.

    Hope this helps.



    "Oke Fireberg" <noll.spamtele2.se> wrote in message
    news:3F8018B0.5E3D00B9tele2.se...
    > Hello all.
    >
    > I have a tab-delimited text file named "PERSONS" containing a number of
    > records with
    > -a unique ID
    > -first name
    > -surname
    > -and other information
    > for a number of people.
    >
    > I also have a FMPro5.5 database "BOOKLOANS" containing fields for
    > -unique ID
    > -loaned book(s)
    > -first name
    > -surname
    >
    > Now, when I fill in the unique ID in "BOOKLOANS", I want it to send an
    > SQL query to "PERSONS" and automatically insert (or show) first name and
    > surname in those fields in "BOOKLOANS".
    >
    > How do I do this? I'm on a Mac using OS9.2. I've read help files but
    > could not make it work.
    >
    > ┼ke
    >
    >
    >

    Mimi Guest

  10. #10

    Default Re: SQL query

    Thanks Mimi but they are not two FMPro databases. If that was so, I could
    simply work with relations. But the point is that PERSONS is in a different
    format. Right now it's a text file, which is for practise purposes to learn
    how to use SQL with FMPro. Later it will be a SQL-Server database on a PC.

    ┼ke


    Mimi wrote:
    > 1. Create a relationship between the two databases based on the ID.
    > 2. In the BOOKLOANS db, paste the first name and surname fields onto the
    > layout. Instead of using the BOOKLOANS fields, use the ones from the
    > relationship (you'll see the pulldown menu at the top of the "Insert Field"
    > dialog box. Instead of using "Current file: PERSONS" use Related file: x"
    > where x is the name of the relationship you created.
    > 3. When you type the id of the user in BOOKLOANS, it will match the id in
    > PERSONS and show the user's first and last name.
    >
    > Hope this helps.
    >
    Oke Fireberg Guest

  11. #11

    Default SQL query

    I want to find out if it can use the SQL to query the
    database with the following outcome : -

    <b>(Note: I am now using temporary ID to query the
    database to get the following results. It doesn't look
    progessional and I want to seek the better way to do it!)
    )</b>


    Course Date Venue Duration
    --------------------------------------------
    MS Windows XP 1.2.2004 CC 7
    4.2.2004
    8.2.2004

    MS Office XP 2.2.2004 SC 8
    6.2.2004
    MS Visual.Net 1.2.2004 DC 8
    6.2.2004

    Please also advice the layout of the database for better
    performance.
    Many thank in advance!

    brad Guest

  12. #12

    Default Re: SQL query

    On Wed, 12 Nov 2003 19:35:33 -0800, "brad"
    <anonymousdiscussions.microsoft.com> wrote:
    >I want to find out if it can use the SQL to query the
    >database with the following outcome : -
    >
    ><b>(Note: I am now using temporary ID to query the
    >database to get the following results. It doesn't look
    >progessional and I want to seek the better way to do it!)
    >)</b>
    >
    >
    >Course Date Venue Duration
    >--------------------------------------------
    >MS Windows XP 1.2.2004 CC 7
    > 4.2.2004
    > 8.2.2004
    >
    >MS Office XP 2.2.2004 SC 8
    > 6.2.2004
    >MS Visual.Net 1.2.2004 DC 8
    > 6.2.2004
    >
    >Please also advice the layout of the database for better
    >performance.
    > Many thank in advance!
    I guess your answer would be "yes". There's no way to give you an
    exact query without knowing the table structure, and formatting into
    this output isn't a function of the query itself if that's what you're
    asking. And I have no idea what you mean by using a temporary ID to
    query the database.

    As for design of the database, we'd need to know more about the
    entities in the database and their relationship. This is a pretty
    typical scheduling database, you can likely find examples if you
    search. You might post more imnformation in a database design
    newsgroup for help, you don't even mention the database you use.

    Jeff
    Jeff Cochran Guest

  13. #13

    Default Re: SQL query

    brad wrote:
    > I want to find out if it can use the SQL to query the
    > database with the following outcome : -
    What database? You're not alone. Nobody seems to realize how important it is
    to tell us the type and version of database they are using.
    >
    > <b>(Note: I am now using temporary ID to query the
    > database to get the following results. It doesn't look
    > progessional and I want to seek the better way to do it!)
    > )</b>
    >
    >
    > Course Date Venue Duration
    > --------------------------------------------
    > MS Windows XP 1.2.2004 CC 7
    > 4.2.2004
    > 8.2.2004
    >
    > MS Office XP 2.2.2004 SC 8
    > 6.2.2004
    > MS Visual.Net 1.2.2004 DC 8
    > 6.2.2004
    >
    > Please also advice the layout of the database for better
    > performance.
    > Many thank in advance!
    Without knowing the details of your business I have to resort to some
    guesses. My assumptions, based on this sample data are:

    A course will be held only in one venue
    A course will always have the same duration

    Given that, a header/details design seems warranted:

    Table: CourseHeader
    CourseID
    CourseName
    Venue
    Duration

    Table: CourseDetails
    CourseID
    CourseDate

    You should create a foreign key relationship between the two tables based on
    the CourseID column


    A simple inner join will return the results to the asp page (this will work
    in either Access or SQL Server - if you are using another database, you're
    on your own):

    Select h.CourseID, CourseName, CourseDate,Venue,
    Duration
    FROM CourseHeader AS h
    inner join CourseDetails AS d
    ON h.CourseID=d.CourseID
    ORDER BY h.CourseID, d.CourseDate

    I would encapsulate this query in a stored procedure (SQL Server) or saved
    query (Access). Let's assume you do this and call the query/procedure
    "GetCourseData"


    There are many ways to utilized the results of this query in asp. You can
    use XML, with XSL or a client-side dhtml solution. Here's a method utilizing
    simple asp code:

    dim cn, rs, arData
    set cn=server.createobject("adodb.connection")
    cn.Open "valid connection string for your database"*
    set rs=server.createobject("adodb.recordset")
    cn.GetCourseData rs
    if not rs.EOF then
    arData = rs.GetRows
    end if
    rs.close: set rs=nothing
    cn.close: set cn=nothing
    if isArray(arData) then
    dim curID, newID, sName,sDates,sVenue,sDuration
    dim iRow, i, sHTML
    const cStTD = "<TD valign=""top"">"
    response.write "<table>"
    For iRow = 0 to Ubound(arData,2)
    newID = arData(0, iRow)
    sName = arData(1,iRow)
    sDate = ""
    sVenue = arData(3,iRow)
    sDuration = arData(4,iRow)
    curID = newID
    i = iRow
    Do Until newID <> curID or i > Ubound(arData,2)
    If sDate = "" then
    sDate = FormatDateTime(arData(2,i),vbShortDate)
    Else
    sDate = sDate & "<BR>" & _
    FormatDateTime(arData(2,i),vbShortDate)
    End If
    i = i + 1
    newID = arData(0, i)
    Loop
    response.write "<TR>"
    response.write cStTD & sName & "</TD>"
    response.write cStTD & sDate & "</TD>"
    response.write cStTD & sVenue & "</TD>"
    response.write cStTD & sDuration & "</TD>"
    response.write "</TR>"
    iRow = i - 1
    Next
    else
    response.write "No records were returned"
    end if

    *see [url]www.able-consulting.com/ado_conn.htm[/url] for examples of valid OLEDB
    connection strings - avoid ODBC if an OLEDB provider exists for your
    database


    HTH,
    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

  14. #14

    Default sql query

    Hi
    I got one table and two filelds in that table.

    Data is something like this

    Field1 field2

    m8m1 test1
    m8m1 test2
    m8m2 test
    m8m2 test
    m8m4 testing
    m8m4 testing

    I want to write a query that throws distnict records from
    field1 with field2 Out put should be something like this

    Field1 field2

    M8m1 test1
    M8m1 test2
    M8m2 test
    M8m4 testing


    What would be the sql for that???

    Thanx in advance...
    -dave
    dave Guest

  15. #15

    Default Re: sql query

    SELECT column1, column2 FROM table GROUP BY column1, column2

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]




    "dave" <anonymousdiscussions.microsoft.com> wrote in message
    news:0d9c01c3df12$e3c8aa10$a501280aphx.gbl...
    > Hi
    > I got one table and two filelds in that table.
    >
    > Data is something like this
    >
    > Field1 field2
    >
    > m8m1 test1
    > m8m1 test2
    > m8m2 test
    > m8m2 test
    > m8m4 testing
    > m8m4 testing
    >
    > I want to write a query that throws distnict records from
    > field1 with field2 Out put should be something like this
    >
    > Field1 field2
    >
    > M8m1 test1
    > M8m1 test2
    > M8m2 test
    > M8m4 testing
    >
    >
    > What would be the sql for that???
    >
    > Thanx in advance...
    > -dave

    Aaron Bertrand [MVP] Guest

  16. #16

    Default Re: sql query

    i m just curious what would be the different if i write
    sth like this
    select distinct column1,column2 from table.
    thanx
    dave
    >-----Original Message-----
    >SELECT column1, column2 FROM table GROUP BY column1,
    column2
    >
    >--
    >Aaron Bertrand
    >SQL Server MVP
    >[url]http://www.aspfaq.com/[/url]
    >
    >
    >
    >
    >"dave" <anonymousdiscussions.microsoft.com> wrote in
    message
    >news:0d9c01c3df12$e3c8aa10$a501280aphx.gbl...
    >> Hi
    >> I got one table and two filelds in that table.
    >>
    >> Data is something like this
    >>
    >> Field1 field2
    >>
    >> m8m1 test1
    >> m8m1 test2
    >> m8m2 test
    >> m8m2 test
    >> m8m4 testing
    >> m8m4 testing
    >>
    >> I want to write a query that throws distnict records
    from
    >> field1 with field2 Out put should be something like
    this
    >>
    >> Field1 field2
    >>
    >> M8m1 test1
    >> M8m1 test2
    >> M8m2 test
    >> M8m4 testing
    >>
    >>
    >> What would be the sql for that???
    >>
    >> Thanx in advance...
    >> -dave
    >
    >
    >.
    >
    dave Guest

  17. #17

    Default Re: sql query

    > i m just curious what would be the different if i write
    > sth like this
    > select distinct column1,column2 from table.
    What is sth? I know it's late, but even more so, there's sth 2 b sed 'bout
    writin' good english.

    Anyway, nits aside, not really any difference. With or without a clustered
    index, SQL Server produces an identical query plan for both queries,
    implying that they will perform roughly the same.

    So, it comes down to personal preference.

    IMHO, using group by makes it far easier to efficiently incorporate a count
    into the query, in case you are wondering how many times each set of values
    is duplicated. Or adding a HAVING clause, e.g. to only find duplicates
    (HAVING COUNT(*) > 1).

    I'm always skeptical when I see the DISTINCT keyword, because it certainly
    gives the appearance that that the data is not in normal form.

    Ideally, your table should have a primary key, so you don't have to filter
    out any data. This is a very good step toward a little concept we call data
    integrity.

    For example, if you add a count column, you could just bump the count of the
    existing row, instead of inserting a new, duplicate row. Try the following
    out in SQL Server, and turn Execution Plan on, to see how this will also
    improve performance (as long as you cluster the primary key index, of
    course).




    create table blat(x int, y int)
    create table blat2(x int, y int, cnt int,
    primary key clustered(x,y))

    set nocount on

    insert blat values(1, 2)
    insert blat values(1, 2)
    insert blat values(1, 2)
    insert blat values(2, 3)
    insert blat values(3, 3)
    insert blat values(2, 3)
    go

    -- the same data, with subtotals instead
    -- of individual duplicate rows
    insert blat2 values(1, 2, 3)
    insert blat2 values(2, 3, 2)
    insert blat2 values(3, 3, 1)
    go

    select distinct x,y from blat
    select x,y from blat group by x,y

    select distinct x,y from blat2
    select x,y from blat2 group by x,y
    go

    drop table blat
    drop table blat2
    go




    So, a question, if I may. Why did you ask, if you already had a query that
    returned the correct result? Why didn't you include that as part of your
    initial question, if you were looking for *other* ways? What would your
    follow-up have been if I had replied with the distinct query instead of the
    group by?

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]


    Aaron Bertrand [MVP] Guest

  18. #18

    Default sql query

    hi
    Is it possible to write sql query to fetch records from
    specific record number?

    lets say i have one variable and it stores values such as
    1,2,3 or so...

    if variable contains, i want to fetch records from 1 to
    20.
    if it contains 2, then records should be from 21 to 40.

    for 3, records would be 41 to 60...

    what could be the sql for that?
    i dont wannna fetch whole table and then filtering
    through code..

    any help would be appreciated...
    cheers,
    dave


    dave Guest

  19. #19

    Default Re: sql query

    "dave" <anonymousdiscussions.microsoft.com> wrote in message
    news:b89201c3ec4e$c1091cf0$a501280aphx.gbl...
    > hi
    > Is it possible to write sql query to fetch records from
    > specific record number?
    >
    > lets say i have one variable and it stores values such as
    > 1,2,3 or so...
    >
    > if variable contains, i want to fetch records from 1 to
    > 20.
    > if it contains 2, then records should be from 21 to 40.
    >
    > for 3, records would be 41 to 60...
    >
    > what could be the sql for that?
    > i dont wannna fetch whole table and then filtering
    > through code..
    >
    > any help would be appreciated...
    > cheers,
    > dave
    Here's an article on paging through recordsets:
    [url]http://aspfaq.com/2120[/url]

    Also, please don't multipost:
    [url]http://aspfaq.com/5003[/url]

    HTH
    -Chris Hohmann


    Chris Hohmann Guest

  20. #20

    Default sql query

    Hello
    I'm using sql server 2000 database and trying to execute
    one query but getting error in correct syntax near
    word 'order'...duedate is the field name on which i
    performing sorting...

    below is the sql

    sql = "SELECT TOP 20 rows FROM (SELECT TOP 100 rows FROM
    tablename ORDER BY DueDate ASC) ORDER BY DueDate DESC"

    Can any one suggest wht could be wrong?
    Any ideas would be appreciated..
    thanx
    bhavin
    bhavin Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Query of Query LIKE and Wild Card chars
    By Ramboni in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 3rd, 05:09 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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