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!

closed as off-topic by ypercubeᵀᴹ, Philᵀᴹ, James Anderson, Andriy M, Julien Vavasseur Jul 18 '16 at 8:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, Philᵀᴹ, James Anderson, Andriy M, Julien Vavasseur
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
  • 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.