Re: SQL puzzler with lookup table

Write a subquery that counts the number of distinct "otherInfo" values per state and returns the states that are singletons using "having".

If a state has only one city period, you might want to exclude it from the subquery if you do want the city to show in this case. That's left to the reader as an exercise.

Add a "where state not in <<subquery>>" clause.

Then use a union with another subquery that returns "N/A", state, otherInfo using "where state in <<subquery>>".

In article <8ts5tg$b08$1_at_nnrp1.deja.com>,   jimbo1155_at_my-deja.com wrote:
> Here's something that has me stumped.
> I'm getting a value from a lookup table.
>
> Say this table has a key comprised of city and state and has
> another column, otherInfo, dependent on the key.
> Now, I need entries for all city and state combinations.
> But let's say that for state = 'DE', otherInfo is always the same
> value, so I don't care what city it is.
>
> My task is to keep the lookup table as small as possible. So I would
> like to have just one row for 'DE', and some value I can use for city,
> and a simple SQL query that will return just one row.
>
> Any ideas?
>
> City State otherInfo
> Albany NY x
> Buffalo NY y
> .
> .
> N/A DE z
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 02 2000 - 20:51:45 CST