Hello,
I have been tasked with creating a search function using Microsoft's K2 environment along with SQL Server 2012. I have created a database and a bunch of tables in SQL Server and am now trying to create a Search form.
What I have to do is to create a search form for hospital researchers to view blood samples. I have a table called SAMPLE, which contains sampleId, location, sampletype. I also have a table called PATIENT that contains things like patientId, dateOfBirth, ethnicity, diagnosis, there is also a table called STUDY, that contains studyCode and studyDesc. Finally there is a linking table called PATIENT_SAMPLE which contains studyCode, patientId, sampleId.
What I would like to do is to create a form that allows people to search for samples based on sample information, patient information or study information.
So a scientist might want to search for "All samples where the patient is born after 01/01/2000, is of african origin and is registered on Psoriasis XA study.
I have created a search form that searches on just one table, so how would I create a search where I need to search on several tables at once. If I was writing this in PHP I would just have embedded a SQL query that combines these tables and the search would add WHERE or AND clauses to the end of the query. I was told that I might need to create a stored procedure to do this. If this is the case how would I go about doing this?
Thanks