Union 2 table that have not a same attribute in SQL

Question

I'm doing a SQL assignment and I found that one question ask me to union 2 table together but this two table isn't have a same attribute. The question will show below.

List the union of customers and employees. For the customers, list customer name (first and last) and contact (CustStreet, CustCity, CusState). For the employees, list employee name (first and last) and contact (EmpPhone and EmpEmail).

Ps. May be I'm misunderstand about the question.

Thank you in advance

Answer 1

This is a valid question because both have the same resulting columns: Customer Name and Contact. You can use inline view and aliases to do this.

E.g. (oracle sql syntax)

select FIRST_NAME || ' ' || LAST_NAME CUSTOMER_NAME, CUST_STREET || ', ' || CUST_CITY || ', ' || CUST_STATE CONTACT from SOME_TABLE
union
select FIRST_NAME || ' ' || LAST_NAME CUSTOMER_NAME, EMP_PHONE || ', ' || EMP_EMAIL CONTACT from SOME_TABLE;

Answer 2

Something like that:

SELECT CustFirstName, CustLastName, CustStreet, CustCity, CustState, NULL, NULL 
FROM Customers
UNION
SELECT EmpFirstName, EmpLastName, NULL, NULL, NULL, EmpPhone, EmpMail 
FROM Employees

Answer 3

You can make a union whitouth problems if the type of the merged columns are the same... so you can left a column empty usin a empty string or a null value

Select CustomerFirst + ' ' + CustomerLast, CustStreet, CustCity, CusState,'','' from Customers
union all
Select EmployeeFirst + ' ' + EmployeeLast, '','','', EmpPhone, EmpEmail from Employees

Note: Not Tested.

Answer 4

On the customers selection just add empty default fields with

null as EmpPhone, null as EmpEmail

and then the same for CustStreet, CustCity, CusState

Be aware the the fields have to be on the same position in the queries of a union.

Answer 5

You can select Null values out as part of your UNION selects. SO select NULL for the two Employee fields that don't exist in Custmer and NUll for each of the 3 Customer fields that don't exist in Employee eg.

SELECT C.FirstName, C.LastName, C.CustStreet, C.CustCity, C.CustState, NULL, NULL FROM Customers C
UNION ALL
SELECT E.FirstName, E.LastName, NULL, NULL, NULL, E.EmpPhone, E.EmpEmail FROM Employees E