How to do a UNION on a single table?

问题:

I need to display the name and surname and address and DOB for all customers who reside in 'Peters' or 'Crows' avenue only.

This is fine I did it like so:

SELECT Customers.FirstName, Customers.Surname,
       Customers.CustomerAddress, Customers.DOB
FROM Customers
WHERE 
(  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%' 
or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%')

but then I read a bit harder and it said:

Use a UNION query to produce the results.

So I read up a bit on UNIONs, but mostly I see that the returned values from both SELECT queries must be of the same length, and normally examples are using 2 different tables?

So I need to perform a UNION on the same table such the all the customers with the words Peters and Crows in their address are shown. I tried:

SELECT Customers.CustomerAddress
FROM Customers
WHERE 
(  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%'
or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%')
UNION
SELECT Customers.FirstName, Customers.Surname,
       Customers.CustomerAddress, Customers.DOB
FROM Customers

But I get the Error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

which is understandable because my first SELECT only returns 3 results (i.e the results I'm looking for) while the other returns all the addressed (including the ones I need).

So my exact problem is, How do I do I perform a UNION on the same table (Customers total of 10 records) so that all the customers with the words Peters and Crows in their address are shown? (3 of the records match the condition the other 7 dont)

回答1:

 SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress
 FROM Customers
 WHERE Customers.CustomerAddress LIKE '%'+ 'Main' + '%'  
 UNION
 SELECT Customers.FirstName, Customers.Surname,  Customers.DOB, Customers.CustomerAddress
 FROM Customers
 WHERE Customers.CustomerAddress LIKE '%'+ 'Gladys'+ '%'

In a union, the two or more queries should always have the same number of fields in the SELECT statement. The WHERE clause seemed to be the problem in your union query.

回答2:

You need both of the returned sets to have the same format if you want to be able to merge the two sets. The first set only returns a customer address, for example:

123 Main St

But the set returned from the second query returns three columns: first name, last name, dob. For example:

John, Doe, 1970-12-31

So get both queries to return the same set of columns so that they can be merged. For example, include all 4 columns:

Jane, Smith, 1975-11-22, 123 Main St

and

John, Doe, 1970-12-31,  89 Elm St

With the same number of columns, in the same order, with the same types, then the two sets can be merged with the UNION.

Jane, Smith, 1975-11-22, 123 Main St
John, Doe, 1970-12-31,  89 Elm St

Also be sure to read up and distinguish between UNION and UNION ALL.

回答3:

I think the problem itself was not exactly the where clause, but the fact that you tried to do a UNION on the results of two queries where you are demanding different sets of data. I am probably wrong but as I understand it, what UNION does is kind of pasting two tables vertically so they have to have the same shape if you will to work properly.

The first part of your query returns just the CustomerAddress of the customers matching your criteria:

SELECT Customers.CustomerAddress
FROM Customers
WHERE 
(  Customers.CustomerAddress LIKE '%'+ 'Peters' + '%'
or Customers.CustomerAddress LIKE '%'+ 'Crows'+ '%') 

Whereas, this query:

SELECT Customers.FirstName, Customers.Surname,
       Customers.CustomerAddress, Customers.DOB
FROM Customers

returns four different columns and has no restriction, which would not prevent the union from happening given that your first query had 4 columns as well, but would not show what you were looking for