Re: Best way to "and" from a one-to-many joined table?

Hi,

This is how I do it, and it runs fast:

select p.*
from test_people p inner join test_attributes a on p.people_id =
a.people_id
where a."attribute" = @firstAttr or a."attribute" = @secondAttr

If you have many attributes to search for you can replace the where part
with

where a."attribute" in (@firstAttr,@secondAttr,...)

For best results, you can index the field "attribute" on test_attributes
table. Be aware of case sensitivity of PG text search.

Best regards,

Milan Oparnica

Oliveiros Cristina wrote:
> Howdy, Bryce
>
> Could you please try this out and tell me if it gave what you want.
>
> Best,
> Oliveiros
>
> SELECT person_name
> FROM test_people p
> JOIN test_attributes a
> ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr))
> JOIN test_attributes b
> ON ((b."people_id" = p."people_id") AND (b."attribute" = @secondAttr));
>
> ----- Original Message -----
> *From:* Bryce Nesbitt <mailto:bryce2(at)obviously(dot)com>
> *To:* sql pgsql <mailto:pgsql-sql(at)postgresql(dot)org>
> *Sent:* Friday, December 05, 2008 6:55 PM
> *Subject:* [SQL] Best way to "and" from a one-to-many joined table?
>
> Dear Experts,
>
> I'm looking for a good technique to do "and" searches on one-to-many
> joined tables. For example, to find people with both 'dark hair'
> and 'president':
>
> # select * from test_people join test_attributes using (people_id);
> +-----------+-------------+---------------+
> | people_id | person_name | attribute |
> +-----------+-------------+---------------+
> | 10 | Satan | The Devil |
> | 9 | Santa | Imaginary |
> | 8 | Obamba | Dark Hair |
> | 8 | Obamba | Dark Hair |
> | 8 | Obamba | USA President |
> | 10 | Satan | Dark Hair |
> +-----------+-------------+---------------+
>
> # select person_name from test_people where people_id in
> (select people_id from test_attributes where attribute='USA President'
> INTERSECT
> select people_id from test_attributes where attribute='Dark Hair');
>
> # select person_name from test_people
> where people_id in
> (select people_id from test_attributes where attribute='USA President')
> and people_id in
> (select people_id from test_attributes where attribute='Dark Hair');
>
> # select people_id,count(*) as count from test_people
> join test_attributes using (people_id)
> where attribute='Dark Hair' or attribute='USA President'
> group by people_id having count(*) >= 2;
>
>
> A postgres specific solution is OK, but SQL92 is better. I had the
> "in" solution recommended to me, but it's performing dramatically
> poorly on huge tables.
>
> Thanks for any references to a solution! -Bryce