List:General Discussion« Previous MessageNext Message »
From:Martijn Engler Date:May 4 2009 10:09am
Subject:Re: SELECT of records that have a matching record in a many to many
table
View as plain text  
If I understand your question correctly (and I'm really not sure about
that), you're using a LEFT JOIN where you actually want to use a RIGHT
JOIN.
Or you need to rewrite your query and set the tables in another order.

On Thu, Apr 30, 2009 at 23:01, Nigel Peck <nigel.peck@stripped> wrote:
>
> 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
> To unsubscribe:
>  http://lists.mysql.com/mysql?unsub=1
>
>
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