• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • 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
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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