How could i combine two (or more) tables into a temporary table?-CodePudding

There's a code, in which i could join all 5 tables together and it works fine. But i want to to make a temporary table from it so it could be easier for me to work next with this lines, and with create temporary table it doesn't work. So could you show me how to do it properly?

select * from city
    join shipment sh on city.city_id=sh.city_id
    join customer cus on sh.cust_id=cus.cust_id
    join driver dr on sh.driver_id=dr.driver_id
    join truck tr on  sh.truck_id=tr.truck_id

The error is

Duplicate column name 'city_id'

CodePudding user response：

To create a temporary table, you must have the CREATE TEMPORARY TABLES privilege. - Read about it here: https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

When you have that, you can do something like this:

CREATE temporary TABLE name_of_temp_table
  SELECT *
  FROM   city
         JOIN shipment sh
           ON city.city_id = sh.city_id
         JOIN customer cus
           ON sh.cust_id = cus.cust_id
         JOIN driver dr
           ON sh.driver_id = dr.driver_id
         JOIN truck tr
           ON sh.truck_id = tr.truck_id

However, there is also an option to create a view such as:

https://dev.mysql.com/doc/refman/8.0/en/create-view.html

CREATE VIEW name_of_view
AS
  SELECT *
  FROM   city
         JOIN shipment sh
           ON city.city_id = sh.city_id
         JOIN customer cus
           ON sh.cust_id = cus.cust_id
         JOIN driver dr
           ON sh.driver_id = dr.driver_id
         JOIN truck tr
           ON sh.truck_id = tr.truck_id

CodePudding user response：

The problem is that you have columns of the same name in several tables. To avoid this from causing troubles, you will need to list your columns, like

select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
from information_schema.columns 
where TABLE_NAME in ('city', 'shipment', 'customer', 'driver', 'truck') 
order by COLUMN_NAME, TABLE_NAME;

You will need to find out which of them are duplicated.

Actually you can generate your query parts like this:

select CONCAT('insert into yourtable(', GROUP_CONCAT(COLUMN_NAME), ') values ' GROUP_CONCAT('(''', existing.TABLE_NAME, '''.''', existing.COLUMN_NAME, ''')'))
from information_schema.columns existing
left join information_schema.columns notexisting
on existing.TABLE_NAME in ('city', 'shipment', 'customer', 'driver', 'truck') and
   notexisting.TABLE_NAME in ('city', 'shipment', 'customer', 'driver', 'truck') and
   CONCAT('''', existing.TABLE_NAME, '''.''', existing.COLUMN_NAME, '''') < CONCAT('''', notexisting.TABLE_NAME, '''.''', notexisting.COLUMN_NAME, '''')
where nonexisting.TABLE_NAME is null

(untested)

Anyway, you will need to avoid duplicating the columns names in your temporary table's definition.