Re: What is the best way to do attribute/values?

On Aug 25, 2004, at 4:22 AM, Mark Kirkwood wrote:

> select
>     pav1.person_id
> from
>     person_attributes_vertical pav1
> where
>        (    pav1.attribute_id = 1
>         and pav1.value_id in (2,3))
>     or (    pav1.attribute_id = 2
>         and pav1.value_id in (2,3))
>

You know..
It may help if you toss in a group by
ie

select pav1.person_id, count(*) from person_attributes_vertical pav1
where (pav1.attribute_id = 1 and pav1.value_id in (2,3)) or ( ... ) or
(...)
group by pav1.person_id
order by count(*) desc

that should give you the person_id's that matched the most
criteria........
I've used similar things before now that I've thought about it.

If you want an exact match you could put
"having count(*) = $myNumAttributes" in there too.. By definition an
exact match would match that definition..

it has an added side effect of producing "closest matches" when an
exact match cannot be found... granted you may not want that for a
dating site : )

"You asked for a blond female, blue eyes.. but I couldn't find any...
but I *DID* find a brown haired male with brown eyes! Is that good
enough?"

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/