How to do a UNION on a single table?

user_1133011 0
Viewed 45189

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)

union sql-server-2008 sql
3 Answers

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

user_127642740
Related