Tricky Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Tricky Query

Hi, I want to know how to select a particular city from list of states. This is my query. This query selects montgomery from all the states in which it is present. I want it only from state MD. How will the query be? Pls advice. SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name IN(‘MONTGOMERY’)
AND s.state_code IN(‘GA’,’IA’,’ID’,’IL’,’IN’,’KS’,’KY’,’LA’,’MA’,’MD’,’ME’,’MI’,’MN’,’MO’,’MS’,’MT’,’NC’,’ND’,’NE’,’NH’,’NJ’,’NM’,’NY’,’OH’,’OK’,’OR’,’PA’,
‘PR’,’RI’,’SC’,’SD’,’TX’,’VA’,’VT’,’WA’,’WI’,’WV’)
AND c.state_num=s.state_num

SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name=’MONTGOMERY’
AND s.state_code=’MD’
AND c.state_num=s.state_num www.elsasoft.org

also, it’s preferred by most to use the ansi join syntax: SELECT c.city_code, s.state_code
FROM CITY_CODES c
JOIN state_codes s
ON c.state_num=s.state_num
WHERE c.city_name=’MONTGOMERY’
AND s.state_code=’MD’
www.elsasoft.org

finally, this query is hardly "tricky". [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><hr noshade size="1">www.elsasoft.org <br />
Hi, Thanks a lot for your reply. Here is the actual query. SELECT contract_num_ng,STATE_PERFORM, CITY_PERFORM, COUNTRY_PERFORM,order_num_ng,mod_num_ng,PIID,referenced_PIID,contract_status,
contractor_name,component_code,office_code,date_signed,contact_title,dollars_obligated,
contractor_street,contractor_city,contractor_state,contractor_country,contractor_zip
FROM contract_actions_dw
WHERE date_signed BETWEEN ’01-OCT-2004′ AND ’30-SEP-2006′
AND contract_status=’A’
AND contract_num_ng LIKE ‘HHS%’
AND city_perform IN(
SELECT c.city_code,s.state_code
FROM CITY_CODES c,state_codes s
WHERE c.city_name IN(‘FAIRBANKS’,’NORTHPOLE’,’ANCHORAGE’,’GALENA’,’MONTGOMERY’,’TUSCALOOSA’,’TUSKEGEE’,’HUNTSVILLE’,’MOBILE’,’FAYETTEVILLE’,’HOTSPRINGS’,’JONESBORO’,’ELDORADO’,’TUCSON’,’MESA’,’BARSTOW’,’MARINA’,’SEASIDE’,’DEL REY OAKS’,’YUBA CITY’,’NORCO’,’CONCORD’,’SUNNYVALE’,
‘RIVERBANK’,’PORT HUENEME’,’OXNARD’,’RIDGECREST’,’CORONADO’,’SAN DIEGO’,’PASADENA’,’LONG BEACH’,’MOUNTAIN VIEW’,’SAN JOSE’,’COLORADO SPRINGS’,’DENVER’,’MIDDLETOWN’,’WATERBURY’,
‘MILFORD’,’FAIRFIELD’,’WASHINGTON’,’WILMINGTON’,’MILTON’,’PENSACOLA’,’SPRINGFIELD’,’VALPARISO’,’JACKSONVILLE’,’CRAWFORDVILLE’,’KEY WEST’,’FORT WALTON BEACH’,
‘ST PETERSBURG’,’AUGUSTA’,’WARNER ROBBINS’,’ALBANY’,’KINGSLAND’,’HINESVILLE’,’FOREST PARK’,’ATLANTA’,’MARIETTA’,’ATHENS’,’COLUMBUS’,’ROME’,’CEDAR RAPIDS’,’MIDDLETOWN’,
‘DUBUQUE’,’MOUNTAIN HOME’,’SHILOH’,’ROCK ISLAND’,’CENTRALIA’,’FAIRFIELD’,’FOREST PARK’,
‘MARION’,’WAUKEGAN’,’MANHATTAN’,’PARSONS’,’ELIZABETHTOWN’,’PADUCAH’,’LOUISVILLE’,’NEW ORLEANS’,’BOSSIER CITY’,’BATON ROUGE’,’LINCOLN’,’SPRINGFIELD’,
‘CHICOPEE’,’HUGHESVILLE’,’FORT MEADE’,’BETHESDA’,’ABERDEEN’,’ADELPHI’,’KITTERY’,’TOWN OF BRUNSWICK’,’TOPSHAM’,’CHESTERFIELD TOWNSHIP’,’BATTLECREEK’,’CAMBRIDGE’,’FARIBAULT’,’HERMANTOWN’,’KANSAS CITY’,’CAPE GIRARDEAU’,’MERIDIAN’,’PASCAGOULA’,’HELENA’,’MISSOULA’,’HAVELOCK’,’FAYETTEVILLE’,’ALBEMARLE’,’WILMINGTON’,’ASHEVILLE’,’GRAND FORKS’,’LINCOLN’,’PORTSMOUTH’,’EATONTOWN’,’EDISON’,’EWING’,’PENNSAUKEN’,’CLOVIS’,’UNIONDALE’,’AMITYVILLE’,’NEW YORK CITY’)
AND s.state_code IN (‘GA’,’IA’,’ID’,’IL’,’IN’,’KS’,’KY’,’LA’,’MA’,’MD’,’ME’,
‘MI’,’MN’,’MO’,’MS’,’MT’,’NC’,’ND’,’NE’,’NH’,’NJ’,’NM’,
‘NY’,’OH’,’OK’,’OR’,’PA’,’PR’,’RI’,’SC’,’SD’,’TX’,’VA’,’VT’,’WA’,’WI’,’WV’)
AND c.state_num=s.state_num
GROUP BY c.city_code,s.state_code
Now the problem is I have a list of companies which exists only in one particular city in a particular state. But my query gives multiple columns for a city matching with many states. But I want only the state and city where the company exists. The company info does not exist in any table in dbase. Just by matching the city and state i have to select the compnany info. Pls advice.
]]>