SQL: Return all the Names when you have all the IDs CodeUnit 28 FEB 2010

A silly one today, but worth recording here on my online memory bank because quite frankly, I forgot one could do it in the first place, and had to rely on Dave in the end to help me right.

So here’s the problem.

I have all the ID values for wildlife parks but instead of displaying the ID values I want to display the park names. These are stored in a table that has two fields, namely ID and the corresponding display name.

Now sure I could loop through each ID and use a plain and simple SELECT displayname FROM parks WHERE parkID = 1 or whatever the ID value is to return each and every name, but I want to simplify this into a single SQL statement because I’m feeling a little lazy today.

So how can this be done?

Well the trick here is to swap out the = operator for the in set operator, meaning we now have a sql statement that looks like this:

SELECT displayname FROM parks WHERE parkID IN (1,2,13,14,21,7)

This will evaluate true for each record where parkID falls into the designated ID set, returning the display name associated with that particular record retrieved record.

Nice. So from running multiple SQL select statments to executing just one, I’d say we’ve solved today’s little problem! :)

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.