[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.

This post has been edited by IsLNdbOi: 22 April 2008 - 01:54 AM

[Glenn9999]

Actually, UNION is where your problem is at. UNION takes similar result sets from all statements, links them together, and removes duplicates. The conditional you gave does not apply to the first two result sets, so all data are returned. Assuming the intent of this statement matches your requirements, provide the conditional to all three result sets.

[Synomenon]

So I need to put: WHERE city LIKE '[A-C]%'

in all three like this?


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;

---------------------------------

Scratch that. Just tried the above and am getting an "Empty Set".

Anyone have more suggestions?

This post has been edited by IsLNdbOi: 22 April 2008 - 02:50 PM

[Glenn9999]

WHERE city LIKE '[A-C]%'

OK I apologize, my eye didn't catch this. As far as I know SQL, this is not standard - for the way I read this, you are looking for cities that start with "[A-C]", which isn't really what you are wanting.

You can do three like conditions, or what I would be tempted to try (test on only one of the statements, btw, that would have revealed this problem as well) is to use SubString and between if the Substring is acceptable in a WHERE clause.

[Synomenon]

Thanks again Glen. Yes, I want the query to list all the cities from the three city columns in the three different tables. I only want the cities thought that start with the letter A, B or C. I'm new to SQL though. How do I use a SubString or Between?

This is what I've tried:

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

This post has been edited by IsLNdbOi: 22 April 2008 - 08:08 PM

[uid0]

Try changing each where clause to
WHERE LEFT(city,1) BETWEEN 'A' AND 'C'

[Glenn9999]

uid0, on Apr 23 2008, 06:50 AM, said:

Try changing each where clause to
WHERE LEFT(city,1) BETWEEN 'A' AND 'C'

That was what I was thinking. Substring is like left, depending on the DB you are on.

WHERE SUBSTR(city, 1, 1) BETWEEN 'A' and 'C'

was what I was talking about.