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

[Oliveiros Cristina]

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