loafbit loafbit - 7 months ago 14
SQL Question

mysql tables union and new column

Give two following tables,

phone table:

PID NAME
1001 iphone
1002 samsung
1003 sony


pc table:

CID NAME
1001 HP
1002 XPS


how can i get the result table?

DID TYPE NAME
1001 PHONE iphone
1002 PHONE samsung
1003 PHONE sony
1001 PC HP
1002 PC XPS


I think this should use UNION, but how to deal with the TYPE?

Answer

You should use UNION ALL if you want to duplicates result also, Otherwise you can use UNION to eliminate duplicate values.

Your query will be like this,

select pid as DID,'PHONE' as TYPE,name from phone
UNION all
select CID as DID,'PC' as TYPE,name from PC;

This will select all records from both tables.