CODEWITHSUNDEEP
sql
Josh
Josh

Reputation: 1

SQL question: How to get a list of cities?

Pretend there is a database with every city in the world and a unique ID to go with each one. I have a list of 50 IDs in one column of an excel document.

How do I return the names of the 50 cities most efficiently? Do I really need to do a WHERE clause with ID# OR ID# ....etc?

Upvotes: 0

Views: 1583

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

In Excel, create a formula that is essentially (assuming the empty cell is A1):

empty    
1         =B1&","&A2
2         =B2&","&A3
3         =B3&","&A4

You can write the formula once and copy it down.

The results will look like:

1         ,1
2         ,1,2
3         ,1,2,3

Go to the 50th row and copy the formula.

Next, paste them into a query in your favorite GUI:

select c.*
from cities c
where cityid in (<paste list here>);

Remove the first comma.

Run the query.

Upvotes: 0

Simon
Simon

Reputation: 537

In SQL terms you would be looking for something like SELECT names FROM tablofcities

This code assumes that you want them in the same order that they are listed in.

Because you mention that these are listed in a excel document rather than a database, it makes it a little different.

I'd recommend checking out the following link for more details on your question.

How to run a SQL query on an Excel table?

Upvotes: 0

zip
zip

Reputation: 4061

You just do a in 

select cityname  from tableofeverycity a where id in (select id from tbl50 ids)

Upvotes: 1

Related Questions