ASP Search

Discussion in 'ASP General' started by JP SIngh, Mar 9, 2005.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    I am trying to build a search results page for the user.

    In the database we have three tables

    Employees

    Empname - Text
    Fullname - Text

    Records

    Id - Autonumber
    Subject - Text
    Description - Text

    Record Dependents

    RecordId - Integer
    Username - Text

    In record dependents there could be multiple values for each recordid
    basically it specifies who is allow to view a particular record.

    What I need help with is how do a search that will find what the user has
    specified but only bring back results for which he is a RecordDependent.

    Can this be done through simple sql? or do I need something more.

    I was thinking more on the lines of

    "Select * from records where exists (Select * from recorddependents where
    empname = '8877YYYY') and description ='" & request.form("description") &
    "'"
     
    JP SIngh, Mar 9, 2005
    #1
    1. Advertising

  2. JP SIngh

    Steven Burn Guest

    First of all, don't use "Select *"

    http://aspfaq.com/show.asp?id=2096

    Second, try something along the lines of;

    strSQL = "Select [FIELDS] from Records Where [Condition 1]=[Value 1] AND [Condition 2]=[Value 2]"

    Replacing [FIELDS] with your field names, and [Condition 1] etc, with the respective conditions and values you want to meet.

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    "JP SIngh" <> wrote in message news:...
    > Hi All
    >
    > I am trying to build a search results page for the user.
    >
    > In the database we have three tables
    >
    > Employees
    >
    > Empname - Text
    > Fullname - Text
    >
    > Records
    >
    > Id - Autonumber
    > Subject - Text
    > Description - Text
    >
    > Record Dependents
    >
    > RecordId - Integer
    > Username - Text
    >
    > In record dependents there could be multiple values for each recordid
    > basically it specifies who is allow to view a particular record.
    >
    > What I need help with is how do a search that will find what the user has
    > specified but only bring back results for which he is a RecordDependent.
    >
    > Can this be done through simple sql? or do I need something more.
    >
    > I was thinking more on the lines of
    >
    > "Select * from records where exists (Select * from recorddependents where
    > empname = '8877YYYY') and description ='" & request.form("description") &
    > "'"
    >
    >
    >
     
    Steven Burn, Mar 9, 2005
    #2
    1. Advertising

  3. JP SIngh

    Bullschmidt Guest

    And in case you ever need it here is an example of one query (QueryB)
    based on the results of another query (QueryA):

    QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

    QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
    QueryA & ")"

    But the following is even faster and allows for more than one field to
    be returned in QueryA:

    QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
    tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

    So QueryA would include all the CustID's for customers starting with A.

    And QueryB would include more fields in the customers table (i.e. not
    just the CustID field) for the records returned in QueryA (which was the
    customers starting with A).

    I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
    from the tblSQLA on the left to other tables that have fields you want
    to return.

    Best regards,
    J. Paul Schmidt, Freelance ASP Web Developer
    http://www.Bullschmidt.com
    ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Bullschmidt, Mar 10, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?TGFrc2htaSBOYXJheWFuYW4uUg==?=

    Google search result like site search!! How?

    =?Utf-8?B?TGFrc2htaSBOYXJheWFuYW4uUg==?=, May 5, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    708
    Lucas Tam
    May 6, 2005
  2. Andy
    Replies:
    1
    Views:
    370
    Jack Klein
    Nov 25, 2003
  3. Anand Pillai

    String search vs regexp search

    Anand Pillai, Oct 12, 2003, in forum: Python
    Replies:
    10
    Views:
    613
    Anand Pillai
    Oct 15, 2003
  4. mason66
    Replies:
    0
    Views:
    432
    mason66
    Jul 27, 2006
  5. Abby Lee
    Replies:
    5
    Views:
    450
    Abby Lee
    Aug 2, 2004
Loading...

Share This Page