How to do a UNION on a single table?

 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.


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.