Professional Web Applications Themes

Find result that matches ALL values in array? - MySQL

Using an IN query, I am able to pass something like this to my database to get the values that match at least one of the array values. SELECT * FROM contacts WHERE (work_lat != '' and work_long != '' and id in (select tutor_id from subjects_tutors where subject_id in (17,18,22,26,27,35))) AND ( (contacts.`type` = 'Tutor' ) ) What I need to do is have the query search for results that match all array values. In other words, in the example above I need a tutor who matches subject_id 17,18,22,26,27 and 35. The data model is for a Ruby on ...

  1. #1

    Default Find result that matches ALL values in array?

    Using an IN query, I am able to pass something like this to my
    database to get the values that match at least one of the array
    values.

    SELECT * FROM contacts WHERE (work_lat != '' and work_long != '' and
    id in (select tutor_id from subjects_tutors where subject_id in
    (17,18,22,26,27,35))) AND ( (contacts.`type` = 'Tutor' ) )

    What I need to do is have the query search for results that match all
    array values. In other words, in the example above I need a tutor who
    matches subject_id 17,18,22,26,27 and 35. The data model is for a
    Ruby on Rails application I am developing for a client.

    Any help would be appreciated.

    Thank you!

    -
    Justin Williams
    Owner, Second Gear
    http://secondgearllc.com/
    -
    Check out Porchlight: bug tracking for small teams <http://
    www.porchlightnow.com>

    Justin Guest

  2. #2

    Default Re: Find result that matches ALL values in array?

    Justin Williams wrote: 

    Instead of a subselect (which you almost never have to use), use a series of
    joins thus:

    SELECT *
    FROM `contacts` `c`
    JOIN `subjects_tutors` `st1` ON `c`.`id` = `st1`.`tutor_id` AND
    `st1`.`subject_id` = 17
    JOIN `subjects_tutors` `st2` ON `c`.`id` = `st2`.`tutor_id` AND
    `st2`.`subject_id` = 18
    JOIN `subjects_tutors` `st3` ON `c`.`id` = `st3`.`tutor_id` AND
    `st3`.`subject_id` = 22
    JOIN `subjects_tutors` `st4` ON `c`.`id` = `st4`.`tutor_id` AND
    `st4`.`subject_id` = 26
    JOIN `subjects_tutors` `st5` ON `c`.`id` = `st5`.`tutor_id` AND
    `st5`.`subject_id` = 27
    JOIN `subjects_tutors` `st6` ON `c`.`id` = `st6`.`tutor_id` AND
    `st6`.`subject_id` = 35
    WHERE `c`.`work_lat` != '' and `c`.`work_long` != '' AND `c`.`type` =
    'Tutor'



    Paul Guest

  3. #3

    Default Re: Find result that matches ALL values in array?

    Well, the values will be dynamically populated based on the query
    passed by the Rails application, and it seems somewhat messy to build
    x number of JOINs like that when the number of subject_id's passed is
    variable. Is there another way that may work better?

    Thanks.

    - j


    On Feb 15, 4:49 pm, "Paul Lautman" <com>
    wrote: 





    >
    > Instead of a subselect (which you almost never have to use), use a series of
    > joins thus:
    >
    > SELECT *
    > FROM `contacts` `c`
    > JOIN `subjects_tutors` `st1` ON `c`.`id` = `st1`.`tutor_id` AND
    > `st1`.`subject_id` = 17
    > JOIN `subjects_tutors` `st2` ON `c`.`id` = `st2`.`tutor_id` AND
    > `st2`.`subject_id` = 18
    > JOIN `subjects_tutors` `st3` ON `c`.`id` = `st3`.`tutor_id` AND
    > `st3`.`subject_id` = 22
    > JOIN `subjects_tutors` `st4` ON `c`.`id` = `st4`.`tutor_id` AND
    > `st4`.`subject_id` = 26
    > JOIN `subjects_tutors` `st5` ON `c`.`id` = `st5`.`tutor_id` AND
    > `st5`.`subject_id` = 27
    > JOIN `subjects_tutors` `st6` ON `c`.`id` = `st6`.`tutor_id` AND
    > `st6`.`subject_id` = 35
    > WHERE `c`.`work_lat` != '' and `c`.`work_long` != '' AND `c`.`type` =
    > 'Tutor'[/ref]


    Justin Guest

  4. #4

    Default Re: Find result that matches ALL values in array?

    On 16 Feb, 03:49, "Justin Williams" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > - Show quoted text -[/ref]

    Messy???
    So you program a loop to create all the joins from a single pattern.
    You never even see the resultant query so you don't see any mess. The
    only foreseeable problem is reaching the maximum number of joins
    allowed for your particular installation.

    Captain Guest

  5. #5

    Default Re: Find result that matches ALL values in array?

    "Justin Williams" <com> wrote in
    news:googlegroups.com:
     

    SELECT
    tutor_id
    FROM subjects_tutors
    WHERE
    subject_id IN (17,18,22,26,27,35)
    GROUP BY tutor_id
    HAVING COUNT(subject_id) = 6; -- 6 = number of elements in IN-list
    Felix Guest

  6. #6

    Default Re: Find result that matches ALL values in array?

    Justin Williams (com) wrote:
    : Using an IN query, I am able to pass something like this to my
    : database to get the values that match at least one of the array
    : values.

    : SELECT * FROM contacts WHERE (work_lat != '' and work_long != '' and
    : id in (select tutor_id from subjects_tutors where subject_id in
    : (17,18,22,26,27,35))) AND ( (contacts.`type` = 'Tutor' ) )

    : What I need to do is have the query search for results that match all
    : array values. In other words, in the example above I need a tutor who
    : matches subject_id 17,18,22,26,27 and 35. The data model is for a
    : Ruby on Rails application I am developing for a client.

    : Any help would be appreciated.


    Read up on the ANY IN SOME and ALL keywords which can be used as part of
    the WHERE clause of a select.


    13.2.8.3 Subqueries with ANY, IN, and SOME
    13.2.8.4 Subqueries with ALL

    Malcolm Guest

Similar Threads

  1. storing result in array
    By thecoolone in forum MySQL
    Replies: 8
    Last Post: January 14th, 07:09 PM
  2. HTTPService result to AS?s array
    By paulo fabiano in forum Macromedia Flex General Discussion
    Replies: 5
    Last Post: March 4th, 06:35 PM
  3. Only 1 row of result in array
    By Szar in forum PHP Development
    Replies: 12
    Last Post: November 9th, 05:30 PM
  4. How to find grid's row that matches row in dataset
    By IK in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 15th, 05:40 PM
  5. array data matches but array created in loop doesn't work
    By Reed Law in forum PHP Development
    Replies: 1
    Last Post: August 13th, 04:25 AM

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