> Have a question about MySQL searching. I need to build a query that
> will search multiple fields on multiple tables. I know how to search
> within a table and even make it match across tables, but this
> situation is a little different:
> Two tables, Samples and Tests
> Samples contains various fields, including SampleNum, SampleName
> Tests has various fields, including SampleNum and TestNum
> There is only one Sample entry per sample, there can be several Test
> entries per sample showing which tests a sample contains.
> For this search I want to generate a list of samples that contain
> specific multiple tests. A sample might have test 101, 102, 107, 112,
> etc etc and I want to find a sample with tests 101 and 102 that also
> have a certain string within sample name.
> I've already built this query:
> SELECT t1.SampleNum FROM Samples AS t1, Tests AS t2 WHERE
> t1.SampleName LIKE '%any given name%' AND t2.SampleNum = t1.SampleNum
> AND t2.TestNum = 101;
> So using that I can find samples that have one specific test. What I
> can't figure out is how to write a query that requires several
> specific tests.
> Any help would be appreciated.