Hi guys n gals, I was wondering if someone might have a neat solution for this... Let's say I have a table like this: [MyTable] id entry ----------------------- 1 Entry A 4 Entry B 5 Entry C 2 Entry A 2 Entry D and I want to select the entries from MyTable that have an id and of 1 and 2. I would expect there to be one result: "Entry A". Any ideas how I can do it? I thought I could do it with an intersect, but MS Access doesn't support intersect statements. Also (just to make things trickier), it should be scable, in that I may wish to only display the entries that have 20 id's - so I'd like to avoid whopping great nested SQL statments for each of the 20 ids. Any ideas would be greatfully received!
Do you want all entries that have 2 id's, or only the ones that have the id's 1 and 2 (excluding for example an entry with id 1 and 4) ?
Think you are simply looking for the unique entries in the entry column where the id is within a certain list of values? Surely you would just use something like SELECT DISTINCT Entry FROM MyTable WHERE ID = 1 ?
I'm not sure if it's possible to create a scalable statement which would allow for an abitrary number of unique ID's. The following statement works when there are two ID's (specifically, 1 and 2): SELECT Entry FROM MyTable WHERE (ID=1) AND (Entry IN (SELECT Entry FROM MyTable WHERE ID=2)); Code (markup): I'm no SQL expert though. The solution you seek might be possible.
This might work (didn't test it though) to get all entries that have all specified id's: SELECT entry FROM tablename WHERE id IN (1, 2) GROUP BY entry HAVING COUNT(*) = 2 Code (markup): You can dynamically create the list of id's for the IN statement, and put the number of id's in the COUNT statement. If you want to get all entries that have a certain number of id's (but you don't care which id's), then just take out the IN statement: SELECT entry FROM tablename GROUP BY entry HAVING COUNT(*) = 2 Code (markup):