List:General Discussion« Previous MessageNext Message »
From:Hugues Ferland Date:April 14 1999 12:10pm
Subject:Re: multiple tables search (sql question)
View as plain text  
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.


Thread
multiple tables search (sql question)Magnet.Com AG14 Apr
  • Re: multiple tables search (sql question)Hugues Ferland14 Apr
  • Re: multiple tables search (sql question)Colin McKinnon14 Apr