SQL/Pro*C problem

Hi,

I'm not sure whether it is a right place to ask, so excuse me if I'm wrong.

I have a table "choices" with two columns: person_id NUMBER
choice_id NUMBER
(both are not unique)

I'm trying to get a list of person_ids who have the same set of choices given by user at the run time. I don't know neither how many nor what choices will be in the set, so I have to generate the SQL statement on the fly. Let's say user wants all people with choices 23, 45 and 76. I have two ideas:

SELECT person_id
FROM choices a, choices b, choices c
WHERE a.choice_id = 23 AND b.choice_id = 45 AND c.choice_id = 76

or:

SELECT person_id FROM choices WHERE choice_id = 23 INTERSECT
SELECT person_id FROM choices WHERE choice_id = 45 INTERSECT
SELECT person_id FROM choices WHERE choice_id = 76

Both ideas seem ugly since I don't know how many "choices" user wants to select, and the table is really big. Hence several questions:

1. Is there a more elegant way to do the same thing?
2. Is there a way to use generated queries (in char* strings) in a Pro*C program? I don't want to mess with OCI, another choice might be stored PL/SQL procedure?
3. Let's say I go with the second statement (I think it should be more efficient than the first one). How can I get a count(person_id)? I have the following (also ugly) idea:

SELECT count(DISTINCT person_id) FROM choices WHERE person_id IN (

	SELECT person_id FROM choices WHERE choice_id = 23
	INTERSECT
	SELECT person_id FROM choices WHERE choice_id = 45
	INTERSECT
	SELECT person_id FROM choices WHERE choice_id = 76

Hope I explained my problem. Any help would be appreciated.

Thanks,

Alex Received on Wed Apr 09 1997 - 00:00:00 CDT