Tuksn Tuksn - 15 days ago 6
MySQL Question

Output tablename of two tables with other attributes

If I have two tables like this:

-- -----------------------------------------------------
-- Table `mydb77`.`supplier`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb77`.`supplier` (
`sp_id` INT(11) NOT NULL AUTO_INCREMENT,
`sp_company` VARCHAR(45) NULL DEFAULT NULL,
`sp_location` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`sp_id`));

-- -----------------------------------------------------
-- Table `mydb77`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb77`.`customer` (
`cu_id` INT(11) NOT NULL AUTO_INCREMENT,
`cu_name` VARCHAR(45) NULL DEFAULT NULL,
`cu_title` VARCHAR(15) NULL DEFAULT NULL,
`cu_location` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`cu_id`);


how can I get the following output:

enter image description here

Ordered by name.

The tables have a different number of columns.

Answer

You can do what you want with union all:

select 'supplier' as tablename, sp_id as id,
       sp_company as name, sp_location as location
from supplier
select 'customer', cu_id, cu_company, cu_location
from customer
order by 3;
Comments