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.