|
|
The mission is to create phone list of customers that
lives in the same city as Jessica Simpson.
The query involves subquery that returns only one value, and most of DBMS
are supposed to execute it quickly:
SELECT CustomerID, CustomerName, phone1
FROM Customers
WHERE CustomerName <> 'Jessica Simpson'
AND City =
(SELECT City
FROM Customers
WHERE CustomerName='Jessica Simpson')
|
Yet if you like JOINs, you can do it joining two replicas of Customers table:
SELECT a.CustomerID, a.CustomerName, a.phone1
FROM Customers AS a INNER JOIN Customers AS b
ON a.City = b.City
WHERE b.CustomerName='Jessica Simpson'
AND a.CustomerName<>'Jessica Simpson'
|
The result will be like this in both cases:
| CustomerID | CustomerName | phone1 |
| 4 | Pestiana Oliviera | 354-523147 |
| 10 | Erika Nass | 354-257413 |
| 18 | Eugenio Banderas | 354-654789 |
| ... | ... | ... |
|