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
arthurh88Asked:
Who is Participating?
 
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,

check this

SELECT A.*
FROM USERS A INNER JOIN Cities B
ON A.City = B.CityName
WHERE B.CITYOPEN = 0
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.