• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

help with SQL query

Hello, thank you for helping me with this MSSQL problem:

Table Cities:  CITYNAME, CITYOPEN  (1 means it is open, 0 means it is closed)
Seattle 1
Miami 1
Chicago 0
Phoenix 1
Columbus 1
Atlanta 0
Portland 1

Table USERS:   USERID, City  
1000  Portland
1001  Seattle
1001  Miami
1002  Atlanta
1002  Portland
1002  Phoenix


What I want to do is, given a USERID, say userID 1002, I want to run a check to see if any of that user's cities are closed.   So if USERID 1002 has even a single closed city, then return "CLOSED + CITY NAME(s)", otherwise "OPEN"

How can I do this in a SQL statement?

Examples:
UserID:
1000 = OPEN
1001 = OPEN
1002 = CLOSED ATLANTA
0
arthurh88
Asked:
arthurh88
  • 2
  • 2
2 Solutions
 
Bhavesh ShahLead AnalysistCommented:
Hi,

check this

SELECT A.*
FROM USERS A INNER JOIN Cities B
ON A.City = B.CityName
WHERE B.CITYOPEN = 0
0
 
jimyXCommented:
Try this one please:
select U.UserID, (Case C.CITYOPEN when 0 then 'Closed '+ C.CITYName ELSE 'Open' End)
From Cities C join USERS U on U.City = C.CITYNAME
Where U.UserID = 1002;
0
 
Bhavesh ShahLead AnalysistCommented:

Hi.

Thanks Jim...


Sory author, didnt read Q fully...

between you want all closed city in one row?
0
 
arthurh88Author Commented:
hi thank you.  Between both of you i got it working.
0
 
jimyXCommented:
Cheers
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now