Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    8

    Question Unanswered: Searching a table based on results from a rs

    I am having an issue creating a SQL query to get the data I want an populate a record set. I will explain the layout of the mySQL table first, then tell you what I am trying to do.

    The layout is like follows, some fields removed for simplicity here:

    1) refnumber (containts either a Policy# or Acct#)
    2) typeofrec (can be DN, PP, FA)
    2) policycount (count of avaliable policy numbers)
    3) policy1
    4) policy2
    ......
    12) policy9

    The policy1-9 fields contain policy numbers that correspond to the acct# being held in the refnumber field. This only has data if the typeofrec field is a DN. if it is a PP or FA the refnumber field contains the PolicyNumber, and the policycount and policy1-9 fields are all set to "0".

    What I am trying to do is search by ACCT#, which will look at the refnumber and if it finds a match it will pull out the values for Policy1-9...and then build the SQL query based on the refnumber and the policy1-9 field. The problem is, if there is more then one record with that ACCT# (they can be from different dates), it only selects the policy1-9 values for the last record, not any of the other ones. Any one have an idea how I could build this query to take into account what I have stated above??

    I have a policy # search working fine, because all I have to do is search the refnumber & policy1-9 fields to get the data.

    I also need to be able to do partial number searches on acct#, but if I can get the problem stated above fixed it shouldn't be an issue getting the partial searches to work.

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    desc the tables used please

Posting Permissions

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