Search two tables and display results

Discussion in 'ASP General' started by JP SIngh, Jan 18, 2006.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    A tricky issue wonder if someone can help

    I have two tables like this

    TapeRecords

    TapeID - Integer Primary key
    Title - Text

    TapeDetails

    RecordId (Primary Key)
    TapeId (related to the taperecords table)
    ProgramDetails (Text)

    The tape details table has a record of the programs that are stored on each
    tape and each tape we have about 20 programs.

    What i want my users it to allow them to search by programdetails and
    display the list of tapes that match that search. I am not able to built a
    query to search and display at the same time. Can someone help?

    I am okay with ASP programming so if anyone has any suggestions or sample
    code i will be pleased to hear from you.
     
    JP SIngh, Jan 18, 2006
    #1
    1. Advertising

  2. "JP SIngh" <> wrote in message
    news:%...
    > Hi All
    >
    > A tricky issue wonder if someone can help
    >
    > I have two tables like this
    >
    > TapeRecords
    >
    > TapeID - Integer Primary key
    > Title - Text
    >
    > TapeDetails
    >
    > RecordId (Primary Key)
    > TapeId (related to the taperecords table)
    > ProgramDetails (Text)
    >
    > The tape details table has a record of the programs that are stored on
    > each tape and each tape we have about 20 programs.
    >
    > What i want my users it to allow them to search by programdetails and
    > display the list of tapes that match that search. I am not able to built a
    > query to search and display at the same time. Can someone help?
    >
    > I am okay with ASP programming so if anyone has any suggestions or sample
    > code i will be pleased to hear from you.
    >
    >


    This will search both the title and the description:

    select taperecords.title
    FROM taperecords, tapedetails
    WHERE taperecords.tapeid= tapedetails.TapeId
    AND
    (taperecords.title LIKE '%searchstring%' OR tapedetails.ProgramDetails LIKE
    '%searchstring%')

    --
    John Blessing

    http://www.LbeHelpdesk.com - Help Desk software priced to suit all
    businesses
    http://www.room-booking-software.com - Schedule rooms & equipment bookings
    for your meeting/class over the web.
    http://www.lbetoolbox.com - Remove Duplicates from MS Outlook, find/replace,
    send newsletters
     
    John Blessing, Jan 18, 2006
    #2
    1. Advertising

  3. JP SIngh

    Guest

    Well I'd suggest creating one SQL statement containing both tables
    using an INNER JOIN or LEFT JOIN.

    And as far as criteria goes hopefully these can help:

    Classic ASP Design Tips - Search Criteria on Multiple Fields
    http://www.bullschmidt.com/devtip-searchcriteria.asp

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-searchmultiplefields.asp

    Best regards,
    -Paul
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips
     
    , Jan 23, 2006
    #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. JM
    Replies:
    1
    Views:
    445
    Hans Kesting
    Jul 10, 2006
  2. Mike M
    Replies:
    0
    Views:
    681
    Mike M
    Nov 16, 2007
  3. Paolo

    Two dropdownlist and two tables...

    Paolo, Sep 19, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    157
    Paolo
    Sep 19, 2006
  4. Abby Lee
    Replies:
    5
    Views:
    483
    Abby Lee
    Aug 2, 2004
  5. Craig Keightley
    Replies:
    10
    Views:
    283
    Craig Keightley
    Jun 29, 2005
Loading...

Share This Page