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

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'

On 16 Feb, 03:49, "Justin Williams" <carpea...@gmail.com> wrote:
> 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'- Hide quoted text -
>
> - Show quoted text -

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.

"Justin Williams" <carpeaqua@gmail.com> wrote in
news:1171577188.644550.5490@k78g2000cwa.googlegrou ps.com:

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

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

Justin Williams (carpeaqua@gmail.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