Q:

Create an if statement in for a SQL query

I want to have a view that looks like the following same sample where the data is pulled from 3 different tables

I want to create a query that will select each of the company with their location, rooms and adddress, but if the company has a location in CA I don't want to select any of the other ones in either NY or OR. I seen CASE but I am not certain how to use it in this case since I don't want to enter the name. Right now my query is something like this
 SELECT DISTINCT TOP (100) PERCENT
     DBO.HOTEL.COMPANY, DBO.HOTEL.LOCATION AS LOCATION, DBO.AMOUNT.ROOMS, 
     DBO.PERSON.OWNERS, DBO.PERSON.ADDRESS
 FROM DBO.HOTEL INNER JOIN DBO.HOTEL.ID = DBO.AMOUNT.HOTEL_ID INNER JOIN
      DBO.HOTEL.ID = DBO.PERSON.HOTEL_ID
 WHERE (LOCATION = 'OR' OR LOCATION = 'CA' OR LOCATION = 'NY') AND
       DBO.HOTEL.OPEN_DATE >= 01/01/2015

A:

You can use NOT EXISTS on this context:
SELECT DISTINCT TOP (100) PERCENT
  DBO.HOTEL.COMPANY,
  DBO.HOTEL.LOCATION AS LOCATION,
  DBO.AMOUNT.ROOMS, 
  DBO.PERSON.OWNERS,
  DBO.PERSON.ADDRESS
FROM
  DBO.HOTEL INNER JOIN DBO.HOTEL.ID = DBO.AMOUNT.HOTEL_ID
  INNER JOIN DBO.HOTEL.ID = DBO.PERSON.HOTEL_ID
WHERE
  LOCATION = 'CA' OR (
    (LOCATION = 'OR' OR LOCATION = 'NY')
    AND 
    NOT EXISTS (
        SELECT * FROM DBO.HOTEL h
        WHERE h.id=DBO.HOTEL.ID AND LOCATION='CA'
      )
    )
  AND DBO.HOTEL.OPEN_DATE >= '01/01/2015'

