Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Join Query on a PK

    I am building a small database that is somewhat like a ledger for pricing team here. In a given month the get 15 cases or so. I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001. Then I have a table that has initials and full names. What I want to do is to be able to rip the RB out and compare that against the table with the names, so the pricers can pull records by their name...
    Ryan
    My Blog

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Try:
    Code:
    SELECT case_nr[1,2] FROM ...
    Don't know if this is standard SQL but it works fine in Informix...

    Regards

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SUBSTRING(casenumber FROM 1 FOR 2)

    also, it will probably help if you would post your questions in the forum specific to your particular database system

    this forum is for standard SQL, the language, and while all database systems support standard SQL to one degree or another, it is in the area of functions that support is most sporadic (to say nothing of the availability of many non-standard but quite useful functions in different databases)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Or simply: SUBSTRING(casenumber, 1, 2)

    In M$ Access you may need to use: LEFT(casenumber,1)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LKBrwn_DBA
    Or simply: SUBSTRING(casenumber, 1, 2)

    In M$ Access you may need to use: LEFT(casenumber,1)
    unless Access automatically doubles up bytes, i would suggest LEFT(casenumber,2)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down


    Ooops, yes that was a typo.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Yet some other systems will need
    Code:
    SUBSTR(casenumber,1,2)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rguy84
    I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001.
    An interesting alternative (especially in terms of performance) could be to define a two-column PK. The first column would then have "RB" and the second one "001", which (1) avoids the costly substring() construct, and (2) allows e.g. having an index on that first column for efficient retrieval. Moreover it's more flexible in that it will easily allow e.g. 3-letter initials in the future without having to change any of your queries (which is not the case now).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    It is an Access DB. But seeing this was strictly how to pull information from a table USING SQL, I figured it was more appropriate to ask a question directly to the SQL Forum.

    Actually it'd be left$(CaseNo,2), but this doesn't work.

    Peter - I think I may have to split them up like you said.
    Ryan
    My Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rguy84
    ... doo bee doo ... USING SQL ... doo bee doo
    there is sql, and there is sql, and there is standard sql...

    ... and then there is access sql

    the whole point about what we are telling you is that unless you know the difference between the various types of sql, and how they are likely to vary from standard sql, it would be far better for you if you would post in the specific forum for your specific database

    just trying to save you the agro, man

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    ok thanks, i'll see what i can do
    Ryan
    My Blog

Posting Permissions

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