CODEWITHSUNDEEP
mysqlsqldatabase
Rahul Kumar
Rahul Kumar

Reputation: 13

How to Join two tables with same attributes?

I have two tables, I want to create a new table with same attribute using the data from two tables. following is the code im trying

/* Create a table called NAMES */
CREATE TABLE NAMES(Id integer PRIMARY KEY, Name varchar(100));
CREATE TABLE GAMES(Id integer PRIMARY KEY, Name varchar(100));
CREATE TABLE JJ(Id integer PRIMARY KEY, Name varchar(100));

/* Create few records in this table */
INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');
INSERT INTO NAMES VALUES(4,'Jane');
INSERT INTO NAMES VALUES(5,'Robert');
INSERT INTO GAMES VALUES(7,'Football');
INSERT INTO GAMES VALUES(6,'Rugby');
COMMIT;

/* Display all the records from the table */
SELECT * FROM NAMES;
SELECT * FROM GAMES;
INSERT INTO JJ (Id, Name) VALUES((SELECT * FROM NAMES), (SELECT * FROM GAMES));

SELECT * FROM JJ; 'Error: near line 21: sub-select returns 2 columns - expected 1'

Upvotes: 1

Views: 1894

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

I would suggest declaring JJ as:

CREATE TABLE JJ (
    Id integer auto_increment PRIMARY KEY,
    Name varchar(100)
);

insert into jj (name)
    select name from names union all
    select game from games;

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

this will work for oracle :

INSERT INTO JJ (Id, Name)((SELECT * FROM NAMES) union all(SELECT * FROM GAMES));

for mysql :

   insert into JJ select * from Names union all select * from Games;

but as Zaynul said when two primary key matches it will throw an error!!!!

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

the way you trying to merge two table is not better aproach b/c two different table primary key may same which will thrown error in times of 3rd table insertion(duplicate key). i would prefer change your table structure make all three table primary key auto increment and push just name not id

INSERT INTO JJ ( Name)
select name from (
SELECT name FROM NAMES
union all
SELECT name FROM GAMES
) t

Upvotes: 0

Related Questions