OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
SELECT of records that have a matching record in a many to many table

From: Nigel Peck (nigel.peckmiswebdesign.com)
Date: Thu Apr 30 2009 - 16:01:50 CDT

Can someone please help me with this one?

I'm trying to SELECT from a table only those records that have a record,
matching a search term, in a table related by a many to many
relationship. The many to many relationship is in a mapping/junction table.

Here's an example of what I have so far:

-=-=-=-=-=-=-=-=-=-=-=-=-

SELECT
`Notes`.`note_id`
FROM
`Notes`

INNER JOIN
`Notes__Districts`
ON
`Notes__Districts`.`note_id` = `Notes`.`note_id`

LEFT JOIN
`Districts`
ON
`Districts`.`district_id` = `Notes__Districts`.`district_id`

WHERE
`Districts`.`name` REGEXP 'bradford';

-=-=-=-=-=-=-=-=-=-=-=-=-

Hopefully someone can see what I'm trying to do here and point me in the
right direction :)

Maybe I need to use a subquery? I've got a feeling I can do this without
that but can't get my head round how to set up the JOINs in this case
with having to use three tables in the one query, I'm only used to two
tables at once.

I couldn't find any tutorials that cover this.

Thanks in advance,
Nigel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql