From: Colin McKinnon
Date: April 14 1999 2:32pm
Subject: Re: multiple tables search (sql question)
List-Archive: http://lists.mysql.com/mysql/1838
Message-Id: <3.0.5.32.19990414153202.008068a0@lonmay>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 13:50 14/04/99 +0100, Magnet.Com AG wrote:
>hello,
>
>I have two tables, one with the address and one with some features for each
>adress, something like that:
>
>table "address":
<snip>
>table "features":
<snip>
>The difficult part is to come: I want to find addresses wich have "feature
>A" and "feature B", this query does not work:
>
>select a_id from address where f_belong_to_addr_id = a_id and f_name
>="feature A" and f_name ="feature B"
This is OT - it's an SQL issue - not a MySQL problem.

But anyway.....

You need to join to 2 instances of the features table:
SELECT a_id 
FROM address a, features f1, features f2
WHERE a.a_id=f1.f_belong_to_addr_id
AND a.a_id=f2.f_belong_to_addr_id
AND f1.f_name ="feature A"
AND f1.f_name ="feature B";

This does not scale well to multiple searches / fuzzy matching - have a
look in the archive for keyword searching and SQL learning resources.

Colin