MSFN Forums > Help w/ simple SQL query.

MSFN Forums > Coding, Scripting and Servers > Server - Side Help (IIS, Apache, etc.)

IsLNdbOi

Apr 22 2008, 01:54 AM

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.

Glenn9999

Apr 22 2008, 04:05 AM

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.

IsLNdbOi

Apr 22 2008, 01:48 PM

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?

Glenn9999

Apr 22 2008, 05:01 PM

CODE

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.

IsLNdbOi

Apr 22 2008, 05:24 PM

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:

CODE

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:

CODE

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

uid0

Apr 23 2008, 05:50 AM

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

Glenn9999

Apr 23 2008, 06:22 AM

QUOTE (uid0 @ Apr 23 2008, 06:50 AM)

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.

CODE

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

was what I was talking about.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.