1

I have been trying to join data from two separate tables with distinct columns into a third table, but

Error code 1054: Unknown Column 'Company' in field list

keeps popping up.

My current code is:

INSERT INTO Company (Year, Company, Revenue, Profits) 
(select Year, Company, Revenue, Profits from 
(select Year, Comp_Name from Comp_Info
union
select Revenue, Profits from Comp_Financials) 
AS tempTable);

Comp_Financials table:

Comp_Financials

Comp_Info table:

Comp_Info Table

Both tables will be combined into the already created table: Company (ID int auto_increment, Year int, Company varchar (20), Revenue int, Profits int, Primary key (ID))

Any help is greatly appreciated!

  • 4
    This is awfully similar to your previous, answered, question: Error Code: 1248 Alias needed in MySQL – ypercubeᵀᴹ Jul 16 '16 at 12:42
  • @ypercubeᵀᴹ It is, but since I am new to MySQL i built the tables in the schema section instead of using query. I changed a few things but got stuck here. – Anthony Potts Jul 16 '16 at 20:22
2

You need to use JOIN by ID instead of the UNION.
Using JOIN there is no need of the sub-query.
Also ( before SELECT is not required after the INSERT INTO.

So the working query is:

INSERT INTO Company (Year, Company, Revenue, Profits) 
SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

More explanation:

What you have tried with UNION is the wrong approach. Let me explain with a small example.

I'm creating the Comp_Info and Comp_Financials are the temporary table for the example

CREATE TEMPORARY TABLE Comp_Info (Id INT, Year INT, Comp_Name VARCHAR (200));

INSERT INTO Comp_Info (Id, Year, Comp_Name) VALUES
(1, 2005, 'Wal-Mart'), (2, 2005, 'Exxon Mobil'), (3, 2005, 'General Motors');

CREATE TEMPORARY TABLE Comp_Financials (Id INT, Revenue INT, Profits INT);

INSERT INTO Comp_Financials (Id, Revenue, Profits) VALUES
(1, 288189, 10267), (2, 270772, 25330), (3, 193517, 2805);

Using the sample data the UNION will result as:

SELECT Year, Comp_Name FROM Comp_Info
UNION
SELECT Revenue, Profits FROM Comp_Financials;

|Year      | Comp_Name       |
|----------|---------------- |
|2005      | Wal-Mart        |
|2005      | Exxon Mobil     |
|2005      | General Motors  |
|288189    | 10267           |
|270772    | 25330           |
|193517    | 2805            |

But with the same sample data, the JOIN will result as:

SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits
FROM Comp_Info AS CI
JOIN Comp_Financials AS CF ON CF.ID = CI.ID;

| Year  | Company           | Revenue   | Profits   
|------ |------------------ |---------- |---------
| 2005  | Wal-Mart          | 288189    | 10267
| 2005  | Exxon Mobil       | 270772    | 25330
| 2005  | General Motors    | 193517    | 2805
| improve this answer | |
  • Thank you very much. That explanation was awesome and it works perfectly! When you type out the code FROM Comp_Info CI JOIN Comp_Financials CF does that define Comp_Info as CI in the code? – Anthony Potts Jul 16 '16 at 20:30
  • @AnthonyPotts Yes. The Comp_Info CI and Comp_Financials CF are same as Comp_Info AS CI and Comp_Financials AS CF. The alias name can be used with/without AS. Anyway updated the same in the answer. – Arulkumar Jul 17 '16 at 4:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.