A single query can achive the desired task.
In SQL, you can put any number of time the same table by using alias.
I would do it like this:
SELECT a.a_id
FROM address a, features b, features c
WHERE
b.f_belong_to_addr_id = a.a_id AND b.f_name="feature A"
AND
c.f_belong_to_addr_id = a.a_id AND c.f_name="feature B"
Only address with both features will be returned.
Hugues
"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":
> a_id int
> a_name char
> etc...
>
> table "features":
> f_id int
> f_name char
> f_belong_to_addr_id int (must be a address_id)
>
> So each address can have several features. I now want to be able to search
> for all addresses which have a specific feature, that is easy:
>
> select a_id from address where f_belong_to_addr_id = a_id and f_name
> ="searched feature"
>
> 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 query results in an empty set because in one entry in "features", only
> one f_name can be possible (not "Feature A" and "Feature B" at the sime
> time). The only way I see to circumvent this is to make two queries and
> then check them by hand (in PERL). But that would be not a nice way.
>
> Any ideas? Thanks a lot for your time.
> Michael Saladin
--
Hugues Ferland
ConnecTalk inc.
