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

[Milan Oparnica]

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:[EMAIL PROTECTED]>
    *To:* sql pgsql <mailto:pgsql-sql@postgresql.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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql