CODEWITHSUNDEEP
mysql
Biribu
Biribu

Reputation: 3833

UNION ALL 2 tables but different columns

I am trying to make a union from 2 tables. Both contains same columns except one of them.

For example:

Table A:

Name, surname, phone

Table B:

Name, surname, otherColumn

I want to make a union from both tables and order them by surname column. It works fine.

But, now I want to know when a row comes from the first table or the second. I thougth result would have Name, surname, phone, otherColumn but just have phone and in that column keeps both results.

Is there a way to keep both columns and fill with nulls when it doesn't exist?

Now, this is my query: 

SELECT id, name, surname ai, phone FROM tableA WHERE status = 0

UNION ALL

SELECT id, name, surname ai, other FROM signedupLocal WHERE status = 0

ORDER BY ai ASC

Upvotes: 0

Views: 572

Answers (2)

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

You can create a blank temporary column for the field that is missing in the other table like 

SELECT id, name, surname, phone, '' as other FROM TableA WHERE status = 0 
UNION ALL 
SELECT id, name, surname,'' as phone,otherColumn FROM TableB WHERE status = 0

Upvotes: 2

naveen goyal
naveen goyal

Reputation: 4629

Try This...

SELECT id, name, surname ai,phone, NULL  as other FROM tableA WHERE status = 0

UNION ALL

SELECT id, name, surname ai, NULL as phone, other FROM signedupLocal WHERE status = 0

ORDER BY ai ASC

Upvotes: 2

Related Questions