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 :-)
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) );
SELECT A.id,B.id, INTO bridge_table --New table is created FROM A,B WHERE A.id = B.id