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" <paul.laut...@btinternet.com>
wrote:
> Justin Williams 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.
>
> > Thank you!
>
> > -
> > Justin Williams
> > Owner, Second Gear
> >http://secondgearllc.com/
> > -
> > Check out Porchlight: bug tracking for small teams <http://
> >www.porchlightnow.com>
>
> 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'