Professional Web Applications Themes

Multi-table search - MySQL

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. ...

  1. #1

    Default Multi-table search

    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.

    Chris Guest

  2. #2

    Default Re: Multi-table search

    Sorry, can you try restating your query in plain English?

    Chris wrote:
    > 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.
    strawberry Guest

  3. #3

    Default Re: Multi-table search

    strawberry wrote:
    > Sorry, can you try restating your query in plain English?
    >
    > Chris wrote:
    >
    >>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.
    >
    >
    Also, your database schema, some sample data and the results you would like
    would help a lot.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: Multi-table search

    Chris wrote:
    > 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.
    Have you thought of using FULLTEXT?


    Paul Lautman Guest

  5. #5

    Default Re: Multi-table search

    Found my own solution, thanks. I don't think it's the best way of doing
    things but it will work until I come up with something better.

    The query looks like this:

    SELECT t1.SampleNum FROM Samples AS t1 WHERE t1.SampleName LIKE
    '%testsample%' AND (t1.SampleNum IN (SELECT t2.SampleNum FROM Tests AS
    t2 WHERE t2.TestNum = '106')) AND (t1.SampleNum IN (SELECT t2.SampleNum
    FROM Tests AS t2 WHERE t2.TestNum = '135')) AND (t1.SampleNum IN
    (SELECT t2.SampleNum FROM Tests AS t2 WHERE t2.TestNum = '138'));

    The query is built in PHP so for each additional test I add another AND
    (t1.SampleNum IN (SELECT t2.SampleNum FROM Tests AS t2 WHERE t2.TestNum
    = '138')) portion.

    Chris Guest

Similar Threads

  1. Can we implement multi-key search in comboBox
    By xiva in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: October 13th, 02:40 PM
  2. Multi search in websites's forums
    By Miki Barzilay in forum PHP Development
    Replies: 0
    Last Post: June 12th, 06:44 PM
  3. [PHP] highlighting multi term search results
    By John W. Holmes in forum PHP Development
    Replies: 7
    Last Post: September 8th, 04:07 PM
  4. highlighting multi term search results
    By Jonas_weber @ Gmx . Ch in forum PHP Development
    Replies: 0
    Last Post: September 7th, 12:05 PM
  5. Multi-table search problem
    By Dave Navarro in forum ASP Database
    Replies: 0
    Last Post: July 30th, 12:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139