Help w/ simple SQL query.

[Synomenon]

I have a simple SQL query that won't work. Well it seems to work, except for the part where I want only cities that begin with the letters A, B, or C to be shown.

Here's my query:

SELECT city "City"
FROM employee
UNION
SELECT city "City"
FROM customer
UNION
SELECT city "CITY"
FROM contact
WHERE city LIKE '[A-C]%'
ORDER BY city;


Basically, all three tables (employee, customer, and contact) have a field / column named "city". I want the query to show me all of the cities from the three tables (with no city repeated) that begin with the letters A, B, or C.

I think it's showing me all the cities, but it's not filtering them out. It's showing me ALL the cities. I just want the ones that start with A, B, or C.

 

[PhatoseAlpha]

The WHERE statement is only applied for the last query. It's not going to spread out across all three in the union so you need the where in every select statement.


SELECT city "City"
FROM employee
WHERE city LIKE '[A-C]%'
UNION
SELECT city "City"
FROM customer
WHERE city LIKE '[A-C]%'
UNION
SELECT city "CITY"
FROM contact
WHERE city LIKE '[A-C]%'
ORDER BY city;

 

[Synomenon]

I tried it and now I just get an "Empty Set".

 

[Synomenon]

When I have the

WHERE city LIKE '[A-C]%'

on just the last SELECT:

SELECT city "City"
FROM employee
UNION
SELECT city "City"
FROM customer
UNION
SELECT city "CITY"
FROM contact
WHERE city LIKE '[A-C]%'
ORDER BY city;


I get this list of cities:

+------------------+
| City |
+------------------+
| Acton |
| Acworth |
| Albany |
| Atlanta |
| Austin |
| Baltimore |
| Bedford |
| Belmont |
| Bohemia |
| Boston |
| Brooklyn Park |
| Buford |
| Burbank |
| Burlington |
| Cambridge |
| Carmel |
| Chattanooga |
| Chicago |
| Cincinnati |
| Clarksburg |
| Columbus |
| Concord |
| Dallas |
| Danbury |
| Denver |
| Detriot |
| Don Mills |
| East Douglas |
| Edmonton |
| Elmsford |
| Emeryville |
| Eugene |
| Fairfax |
| Fargo |
| Fort Wayne |
| Framingham |
| Ft. Wayne |
| Gloucester |
| Hartford |
| Houston |
| Hull |
| Huntington Beach |
| Jackson |
| Jacksonville |
| Kansas City |
| Knoxville |
| Lakewood |
| Landover |
| Laramie |
| LeCroix |
| Lexington |
| Lisle |
| Long Beach |
| Los Altos |
| Los Angeles |
| Madison |
| Mamaroneck |
| Manchester |
| Matthews |
| McLean |
| Miami |
| Middletown |
| Milton |
| Minneapolis |
| Miramar |
| Mississauga |
| Missola |
| Mogadore |
| Nashville |
| Natick |
| Needham |
| New Bedford |
| New Berlin |
| New London |
| New Orleans |
| New York |
| Newton |
| North Miami |
| North Potomac |
| Northbrook |
| Orlando |
| Overland Park |
| Paoli |
| Peoria |
| Plymouth |
| Port Washington |
| Powell |
| Raleigh |
| Reston |
| Rochester |
| Rutherford |
| Salt Lake City |
| San Francisco |
| San Jose |
| San Ramon |
| Santa Fe |
| Sarasota |
| Sioux City |
| South Laguna |
| Spokane |
| St Louis |
| St Paul |
| St. Louis |
| Stow |
| Stowe |
| Syracuse |
| Tacoma |
| Victoria |
| Waltham |
| Washington |
| Watertown |
| Wellesley |
| West Bloomfield |
| West Chicago |
| West Roxbury |
| Westerville |
| Westwood |
| White Plains |
| Wilmington |
| Winchester |
| Winnipeg |
| Winter Park |
| Wood Bridge |
+------------------+
123 rows in set (0.00 sec)

Those are all the cities in the "city" column in all three tables. Some of these cities are in three of the tables, some in two of the tables and some in only one table. The Unions seem to be filtering them correctly since it shows all of the cities only once. However, when I put the

WHERE city LIKE '[A-C]%'


in the last SELECT and the second SELECT:

SELECT city "City"
FROM employee
UNION
SELECT city "City"
FROM customer
WHERE city LIKE '[A-C]%'
UNION
SELECT city "CITY"
FROM contact
WHERE city LIKE '[A-C]%'
ORDER BY city;


it lists only these cities:

+--------------+
| City |
+--------------+
| Acton |
| Atlanta |
| Bedford |
| Belmont |
| Boston |
| Burlington |
| Cambridge |
| Concord |
| Emeryville |
| Framingham |
| Gloucester |
| Houston |
| Lexington |
| Long Beach |
| Milton |
| Natick |
| Needham |
| Newton |
| Stow |
| Waltham |
| Watertown |
| Wellesley |
| West Roxbury |
| Westwood |
| Winchester |
+--------------+
25 rows in set (0.00 sec)

Finally, when I put the

WHERE city LIKE '[A-C]%'


in all three of the SELECTs:

SELECT city "City"
FROM employee
WHERE city LIKE '[A-C]%'
UNION
SELECT city "City"
FROM customer
WHERE city LIKE '[A-C]%'
UNION
SELECT city "CITY"
FROM contact
WHERE city LIKE '[A-C]%'
ORDER BY city;


I get:
Empty set

 

[KLin]

Have you tried each query inidividually to see what results you get from each table?

 

[GilletteCat]

Originally posted by: IsLNdbOi
I tried it and now I just get an "Empty Set".

Have you tried
SELECT city "City"
FROM (employee OUTER JOIN customer ON employee.city = customer.city) OUTER JOIN contact ON customer.city = contact.city
WHERE city LIKE '[A-C]%'

 

[Synomenon]

Just tried it. Getting this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN customer ON employee.city = customer.city) OUTER JOIN contact ON cust' at line 2

 

[GilletteCat]

Originally posted by: IsLNdbOi
Just tried it. Getting this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN customer ON employee.city = customer.city) OUTER JOIN contact ON cust' at line 2

Perhaps, MySQL requires FULL OUTER JOIN instead of just OUTER JOIN. I haven't worked with mySQL in a while.

 

[troytime]

get rid of the parenthesis

FROM employee
OUTER JOIN table ON ....

my brain hurts too much to think or look it up, but you may want to check up on the mysql joins to figure out which join you want to do