Jump to content
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble

MSFN is made available via donations, subscriptions and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, register and become a site sponsor/subscriber and ads will be disabled automatically. 


Synomenon

Help w/ simple SQL query.

Recommended Posts

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 by IsLNdbOi

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by IsLNdbOi

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by IsLNdbOi

Share this post


Link to post
Share on other sites

Try changing each where clause to

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...