From: | "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> |
---|---|
To: | "Bryce Nesbitt" <bryce2(at)obviously(dot)com>, "sql pgsql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Best way to "and" from a one-to-many joined table? |
Date: | 2008-12-05 19:23:25 |
Message-ID: | 00d401c9570e$f1fbcee0$ec5a3d0a@marktestcr.marktest.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
To: sql pgsql
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
From | Date | Subject | |
---|---|---|---|
Next Message | Milan Oparnica | 2008-12-05 20:16:11 | Re: Best way to "and" from a one-to-many joined table? |
Previous Message | Sean Davis | 2008-12-05 19:08:20 | Re: Aggregates with NaN values |