If you have two SQL database tables that are not joined together, how would you create another table to join them.

Question

Harshit Pandey · Accepted Answer

Pretty simple question 

Lets say we have two tables Person and Other school - each person is student in the school, since you need to join those two  tables, you need common column to share and hence forth you find the key to join.

Inner Join - join table to bring records common in both the tables 
Outer Join - Bring results besides the ones that are common or rather bring distingushed results (uncommon) in two tables


I'll write here inner join using MS- SQL 

Question has three  part - create another table , join the reults and then populate the reults 

-- Create the resulting table 
Create Table Result 
{
string name;
}

-- Join to table and insert into result table 

- Assuing two table person and school has common column name

INSERT INTO RESULTS     --whatever be the join result will populate the result table
select  name 
from Person
Inner join School                               -- used inner join here 
ON person.Name= School.Name      -- used the key to join 

-- now lets pull up the results by querying resut table 

Select Name from Result         -- Questions resolved :-)

kimo · Answer

create new table mean that -> the tables are not connected, they don't have a common column but they complete each other. they have many to many relationship  mean that new table will have 2 columns (this is one option :-) ), and the both are they primary keys of  both tables, so they will be presenting the both tables, and they will be primary keys and foreign key for the connecting_tables at the same time. so table 1 and table 2 are called the look up tables and the connection_table called the bridge table.

so the sql code will look something like that :

create table connecting_tables(
id_toPresent_1st_table int(12) NOT NULL,  // the variable type and length should be the identical to the variable type and length of the primary key in the 1st table

id_toPresent_2st_table int(12) NOT NULL,  // the variable type and length should be the identical to the variable type and length of the primary key in the 2st table

//both are primary keys.
PRIMARY KEY(id_toPresent_1st_table, id_toPresent_2st_table),


//both are foreign keys.
CONSTRAINT fk_table_1 FOREIGN KEY (id_toPresent_1st_table_Id)  REFERENCES table_1(table_1_Id),
CONSTRAINT fk_table_2 FOREIGN KEY (id_toPresent_2st_table_Id)  REFERENCES table_2(table_2_Id)
);

sarit · Answer

SELECT A.id,B.id,
INTO bridge_table  --New table is created
FROM A,B
WHERE A.id = B.id

Sigiloso · Answer

Wondering anyone have tried Rooftop Slushie? I've found it useful so far but not sure if it's worth it to make payment. (rooftopslushie.com)

Sigiloso · Answer

CREATE table table_name
as 
SELECT * FROM table1 JOIN table2 USING (pk);

Amazon

Pergunta de entrevista da empresa Amazon

Respostas da entrevista

Empresas seguidas

Buscas de vagas