Finally, I have a third table that links people to groups. Each person can be a member of multiple groups, so the third table links the two. LinkTable = ( personId, groupId, order ) with PRIMARY KEY( personId, groupId ) Given a random list of personIds, what's the fastest way of getting the unique list of all groups they belong to? Currently, I use a precompiled statement along the lines of "SELECT * FROM LinkTable WHERE personId==?;" to get the list for each person, then use STL routines to build up a unique list. Can I do this quickly and efficiently using the SQL engine?
You can use the DISTICNT keyword to get the list of unique groups using a query like:
SELECT DISTICNT groupId FROM LinkTable WHERE personId IN (...)
The tricky part is building the list of personIds. If you have the list available in your code you will need to build and compile an SQL command with the correct personIDs embedded (or you could use the correct number of ? parameters and bind the values, but that just seems like more work). If the list of personIds was obtained from an earlier SQL query, it would be best to repeat that query as a subselect like this example:
SELECT DISTICNT groupId FROM LinkTable WHERE personId IN (SELECT personId FROM People WHERE city = ?)
I hope this helps.
Dennis Cote
