Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

kspence
Starting Member

5 Posts

Posted - 2010-03-20 : 14:39:19

I'm building a job listings site where I want to display a list of cities that have jobs and ignore the ones that don't.

I have two tables -- one that contains all cities and states, and one that contains all of the job listings. The job listings table also has cities and states in it, but the formatting makes it so that I want to use it as reference only.

Here's a pseudo-sql version of the query I want to create -- I just don't know how to construct the syntax. Any advice?

SELECT city_name FROM city_table WHERE city_name LIKE job_location_field IN job_listings_table

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-20 : 15:02:19

[code]
SELECT city_name
FROM city_table
WHERE city_name IN(select job_location_field FROM job_listings_table)
[/code]

Try something like that. I would like to see your table structure including data types. Some of the very smart people here might have some suggestions for you.

===
http://www.ElementalSQL.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-20 : 15:12:52

A join is preferred over a subquery for performance reasons:

SELECT c.city_name
FROM city_table c
JOIN job_listings_table jl
ON c.city_name = jl.job_location_field

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kspence
Starting Member

5 Posts

Posted - 2010-03-20 : 15:18:37

Thanks to you both!

tkizer,
Can you explain the significance of c and jl? Would I replace those with the appropriate table names?

baja_yu
Starting Member

24 Posts

Posted - 2010-03-20 : 16:39:00

No you wouldn't. C and JL are just aliases for tables so its easier/shorter to address them later on. See the last line that says "c.city_name", if you didn't set the alias "C" you would have to write "city_table.city_name" instead. Same goes for JL. What you *should* substitue with the names of your tables are the "city_table" and "job_listings_table" as well as field names "city_name" and "job_location_field".

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-20 : 22:19:53

Thanks, Tara. I knew I was messing it up a little. I almost always learn something new from you!

===
http://www.ElementalSQL.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-20 : 23:26:41

Yours isn't messed up. They would return the same result set. Joins are just more efficient typically.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kspence
Starting Member

5 Posts

Posted - 2010-03-21 : 14:16:54

Thanks for the explanation, baja -- that makes perfect sense.

I'm struggling now with the syntax to search for cities within a given state (Let's say California). I've been trying variants of the following with no success. Again, many thanks for taking the time to explain things to me -- I've learned a ton already.

SELECT c.city_name
FROM city_table c
JOIN job_listings_table jl
ON c.city_name LIKE jl.job_location_field
AND c.state = 'California'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-21 : 14:38:29

In the ON clause I see a LIKE while tara hasn't shown you to do this.
So I think your citynames are not exactly matching to the values in job_location_field.

And if you are saying 'trying variants of the following with no success' then what is the problem?
No results? Error messages?

Maybe the best way is to give:
- table structure
- sample data
- wanted output in relation to sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

kspence
Starting Member

5 Posts

Posted - 2010-03-21 : 15:08:43

The data in the two tables doesn't match exactly. One has 'clean' city and state fields, and the other has comma delimited data. IE: city,state,zip. I'm new at this, but from what I've read, LIKE is most appropriate in this case. Please correct me if I'm wrong, though.

My table structure is this (with a couple sample rows):

JOB LISTINGS TABLE

Job_Title Hiring_Company Location
Cashier McDonalds San Francisco,CA,12345
Cashier Gap Camden,NJ,12345

CITIES TABLE

City State
San Francisco CA
Camden NJ
Newark NJ

I'd like to get all the cities in a given state that have jobs.

So in the above example, let's say I wanted to list cities from NJ that had jobs. I'd want Camden returned, but not Newark.

Does that make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-21 : 19:54:18

SELECT c.city_name
FROM city_table c
JOIN job_listings_table jl
ON c.city_name LIKE '%' + jl.job_location_field + '%'
AND c.state = 'California'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-22 : 02:16:39

I would change it to
ON jl.job_location_field LIKE '%' + c.city_name + '%'
because c.city_name holds the well-defined value.

And this
AND c.state = 'CA'
because the example data shows no 'California'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources