Re: MultiValue Databases

Just a quick comment: there is an extra layer of difficulty here. Thre really is NO need for a phone ID, but this is just for fun so lets see.

no table changes needed. (nor three levels of parentheses)

Let's see: a person with two phone numbers. (not a typical query, since usually finding all owners of one phone usually is sufficient. but we'll platy along a bit. This all is just play and proves nothing.)

John posted a nice solution. Easily extended.

Here's some brute force solutions (elegance takes a little longer). And pardon me if I slip into old style SQL. (e.g., I still prefer Descriptive notation over algebraic in Chess.)

Solution #1 Phone numbers are unique so just count them then use that result to name the man
SELECT person.*
from person where personID in ( SELECT personID from phone INNER JOIN person_phone ON phone.phoneID = person_phone.phoneID
WHERE phone.number in ( "111-1111", "222-2222" ) group by personID
having count(*) =2 ) ;

(Variation change the last clause to:
HAVINGcount(*) >=2)
and remove the WHERE clause to find all persons with 2 or more phones. Try that one Neo.)

Solution #2 find persons with 2 given phones. SELECT person.*
from person, person_phone AS pp1, person_phone AS pp2, phone AS line1, phone AS line2
WHERE line1.number="111-1111" and line1.phoneID = pp1.phoneID

     AND  line2.number="222-2222" and line2.phoneID = pp2.phoneID
     AND person.personID=pp1.personID AND person.personID=pp2.personID

Remove phone tables line1 and line2 and the specific number from the were clause to find ALL persons with exactly 2 phones.

Are there other solutions? sure. but I have some real work to do.

Ed Received on Tue Jun 07 2005 - 21:08:46 CEST