List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:April 30 2009 9:01pm
Subject:SELECT of records that have a matching record in a many to many table
View as plain text  
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

Thread
Is there a MySQL 5.4 Speed advantage for MyISAM tables?mos30 Apr
  • Where the hell did 5.4 come from?Daevid Vincent30 Apr
    • SELECT of records that have a matching record in a many to many tableNigel Peck30 Apr
      • Re: SELECT of records that have a matching record in a many to many tableMartijn Engler4 May
    • Re: Where the hell did 5.4 come from?Andy Shellam30 Apr
      • RE: Where the hell did 5.4 come from?Gary Smith30 Apr
      • Re: Where the hell did 5.4 come from?Shawn Green3 May
  • Re: Is there a MySQL 5.4 Speed advantage for MyISAM tables?mos3 May