I am having trouble figuring out how to create a table linking information from 2 created tables. The new table must consist of the primary keys of table 1 + 2.
How would i create a table using 2 tables? I'm not entirely sure whether or not I should fully combine the two tables or just use 1 table (ex table1) and use a foreign key from table 2.
OR is there another way I should approach this?

table1 information about table1:
xx, .....attributes

table2 information about table2:
yy ........ attributes etc

table3 information linking table 1 + 2

This post was edited by Chesse on Nov 23 2015 06:58pm

sounds like just a many-to-many mapping table? just use their primary keys
table1table2xref:
id (optional surrogate key)
xx (foreign key to table1)
yy (foreign key to table2)

hi thanks for the quick reply,

this is what i got

Create Table ShipPort
(
p varchar(3),
Constraint fk_p_ship Foreign key (p) References ship(shipNo),
constraint fk_p_port Foreign key (p) References port(portId)
);

The ship refers to table 1 and port table 2

Do the values of the primary keys carry over from table 1/2 to 3?
Basically I need 1 column information from each 1 and 2 and have the information on 3.

can you rephrase the question? perhaps with an example?

Sorry :0

I need to link information from table 1 ( Ship ) and table 2 (Port) into one table with information carrying over both tables. ( either some or all of it)
Both primary keys from the tables created need to be in the new table.

ex: ShipPort(shipNo, portId)
shipNo and portId are the primary keys

I need to make this table in order to answer this question:

"list both the names of the ports and the ships
that dock at each port in alpha order"

(the names of the port) are from the port table
the names of the ship are from the ship table

//
Also, would I have to re-insert the values once again or can i carry the column values from the two tables into the new one
If you would like, I could pm you the appendix that shows the information of the ships and ports.

This post was edited by Chesse on Nov 23 2015 08:32pm

first, are you sure that's a many-to-many relationship? i dont see a date anywhere, and i assume a ship can only be in a single port at a single time.

second, i think what you're looking for is the "join" keyword.


this will give you all the ships / ports / names

This post was edited by carteblanche on Nov 23 2015 08:35pm

Thanks for helping me. much appreciated it
I'm not sure what you mean by many to many relationships for this scenario

Here is a small sample of the information I am using to create my tables

http://imgur.com/C8Cshdy

Essentially, the ship and port table will have these information

info about ships Ship
ship# , shipname , speed and tonnage

info about the port Port
portid port country and port name

If i use the select method, will that create a new table for me?

This post was edited by Chesse on Nov 23 2015 09:03pm

i think you're missing the point of the join. the idea is to normalize your data. so your port table would contain a country and name, and you will not put that data anywhere else. if you ever have a portId and want to know the name, then you will join that table to get the info.

Suppose instead you defined portName twice, once in the port table and once in the ShipPort table. what happens if someone decides to rename Gustavia port to Fring? Instead of renaming it in a single Port table (and every app and every query that uses the table would get the update), you would have to rename it in TWO tables. this might not seem so bad at first. but imagine your port table actually has 50 columns instead of 5, so now your ShipPort table has 52 columns. oh, and you actually have 30 different tables which all need Port Name. So now every time you change the name of the port, you have to update it in 30 places. what happens if you miss one?



'select' returns a result set. it does not create a table. if you want to treat a result set like a table, you can create views.

This post was edited by carteblanche on Nov 23 2015 09:08pm

hmmm okay, thanks for helping me

the only thing bothering me is the join method

There aren't any common fields between the two tables and if i do join them. How would the table be formatted?

I had a lot of duplicate data from the chart, but the table only allowed one of the same data.

This post was edited by Chesse on Nov 23 2015 09:19pm

your ShipPort table holds the common fields you join on. i gave a sample query a few posts above


not sure what you're asking? which table are you asking about: ship, port, or shipport? and what "format" are you referring to?

This post was edited by carteblanche on Nov 23 2015 09:43pm