Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Lightbulb Unanswered: select tables where name begins with 'a'

    how would i go about selecting data from a table...using the first letter of the staff_name field...

    eg: i have a list at the top of my page..links, A - Z...for a staff directory page...and i want it so when you click on a link (A - Z), it display's only the people whose name begins with the letter clicked..

    ive tried working my head arround it, using the left function to get the first letter...but i dont know how to structure my queries or my loop...

    would anyone have any suggestions about this??

    Thanks, Justin

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Come on, Justin. I mean, I could give you the answer to this because it is really very simple, but then I don't think you would get much out of it. Post the code that you tried and which did not work, and you will get not only the correct method, but also some valuable tips on SQL programming to boot.
    And for that matter, BEFORE you post your code, read the section in Books Online on SELECT statements, including the WHERE clause.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    Come on, Justin. I mean, I could give you the answer to this because it is really very simple, but then I don't think you would get much out of it. Post the code that you tried and which did not work, and you will get not only the correct method, but also some valuable tips on SQL programming to boot.
    And for that matter, BEFORE you post your code, read the section in Books Online on SELECT statements, including the WHERE clause.
    Like, I coudln'% agre% mor%

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmm....loop?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I like this cascred guy. He's a real wildcard.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you sure you haven't registered twice, under a new alias?

    Loop?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It might be prudent to discuss the difference between left and like

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(myName varchar(50))
    CREATE INDEX myIndex99 ON myTable99(myName)
    GO
    
    INSERT INTO myTable99(myName)
    SELECT 'Adam' UNION ALL SELECT 'Brett' UNION ALL SELECT 'Blindman' UNION ALL SELECT 'cascred' UNION ALL SELECT 'Freefall'
    GO
    
    SELECT myName FROM myTable99 WHERE myName LIKE 'b%'
    
    SELECT myName FROM myTable99 WHERE LEFT(myName,1) = 'b'
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Do a show execution plan and look at the difference between the 2.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Brett Kaiser
    Do a show execution plan and look at the difference between the 2.
    This is the reason I avoid left. Also notice that the optimizer converts a left statement to a substring.

    Bill

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then compare the execution plan of this to your first search statement:

    SELECT myName FROM myTable99 WHERE myName between 'b' and 'c'

    The server parameterizes it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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