--The DDL
CREATE TABLE Cities
(city_id   INT NOT NULL PRIMARY KEY,
 city_name NVARCHAR(15) NOT NULL);

CREATE TABLE Trips
(trip_nbr      INT NOT NULL PRIMARY KEY,
 start_city_id INT NOT NULL,
 end_city_id   INT NOT NULL,
 start_date    DATE NULL 
               DEFAULT GETDATE()
 
 --Check Constraint
 CHECK (start_city_id <> end_city_id),
 
 --Foreign Key Constraints
 CONSTRAINT FK_start_city
  FOREIGN KEY (start_city_id)
  REFERENCES Cities (city_id),
  
 CONSTRAINT FK_end_city
  FOREIGN KEY (end_city_id)
  REFERENCES Cities (city_id)
 );

--The Query
SELECT T.*, 
       C1.city_name AS start_city_name,
       C2.city_name AS end_city_name
  FROM Trips T
  JOIN Cities C1
    ON T.start_city_id = C1.city_id
  JOIN Cities C2
    ON T.end_city_id = C2.city_id;

SELECT T.trip_nbr, 
	   T.start_date,
       C1.city_name AS start_city_name,
       C2.city_name AS end_city_name
  FROM Trips T
  JOIN Cities C1
    ON T.start_city_id = C1.city_id
   AND T.start_date BETWEEN '2000-01-01' AND '2009-30-12'
   AND C1.city_name = 'city1'  
  JOIN Cities C2
    ON T.end_city_id = C2.city_id;

quote:

---

Originally posted by Kristen

Personally I would have put every condition test for [Cities] in the JOIN Cities clause (allows changing the JOIN to LEFT JOIN etc.), and conditions for [Trips] in the WHERE.

---