How to do a UNION on a single table?

Question

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)

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