Search two tables and display results

[JP SIngh]

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.

 

[John Blessing]

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

 

[paul]

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