B1azingPh03nix B1azingPh03nix - 14 days ago 7
SQL Question

Combining two columns from separate tables into one in SQL Oracle SQL developer

So, I'm trying to run a query that requires me to join two separate tables together, but I'm required to consolidate some information into a single column, and tag which table it came from.

Like this...

table_1 table_2
teacher_name student_name
Steve Jimmy
George Craig

Output:

NAME: Title:
Steve Teacher
George Teacher
Jimmy Student
Craig Student


Here's what I have so far...

SELECT x.first_name, x.last_name, y.first_name, y.last_name, x.city, y.city
FROM (SELECT i.first_name, i.last_name, z.city, z.zip
FROM instructor i JOIN
zipcode z
ON z.zip = i.zip
WHERE z.zip = 10025) x

LEFT OUTER JOIN

(SELECT s.first_name, s.last_name, z.city, z.zip
FROM student s JOIN zipcode z
ON s.zip = z.zip
WHERE z.zip = 10025) y
ON y.zip = x.zip


Essentially, all that I need to do is to combine the first and last names from the x and y query into a single column, as well as the city from both, and then create a column that identifies whether or not the person came from the instructor table (x) or the student table (y)

Answer
SELECT teacher_name + teacher_last as Name, city, 'Teacher' as source
FROM TeacherTable       
UNION ALL
SELECT student_name + student_last as Name, city, 'Student' as source
FROM StudentTable       
Comments