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.

Edited April 22, 2008 by IsLNdbOi

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

Edited April 22, 2008 by IsLNdbOi

[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

Edited April 23, 2008 by IsLNdbOi

[uid0]

Try changing each where clause to

WHERE LEFT(city,1) BETWEEN 'A' AND 'C'

[Glenn9999]

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.