Database Question 2

[pierrotsc]

I am new to database implementation. I am reading articles on the internet about it and so far so good. I just have a question to see if what i want to achieve is possible.

I have a database with 2 tables inside.
Table1 has
-ProjectID
-Date
-Project Name
-Paper ID
-Paper Name

Table2 has
-Paper ID
-Paper Name

Table 2 has all kinds of papers and right now has 12 rows.
Now if Paper ID is 10 in table 1, can I pull the paper name from table 2 and put it in table 1?

Thanks.
PO

 

[DjangMan]

Yes.

Do you just want to display the paper's name where there is a match between Table1.PaperID and Table2.PaperID or do you want to update the Table1.PaperName field?

 

[pierrotsc]

I would like when a user pick a paper name from table 2 to populate the paper name field in table 1.

Thanks for any help.
P

 

[DjangMan]

The query to update would be something like:

UPDATE Table1
SET Paper ID=Table2.Paper ID, Paper Name=Table2.Paper Name
FROM Table1
  INNER JOIN Table2 ON Table1.Paper ID=Table2.Paper ID

 

[pierrotsc]

Thanks, I will try that and will let you know.
PO

 

[whosrdaddy]

pierrotsc,

why are you duplicating the paper name field?
this should not be needed, all you need is the PaperID, the name can be retrieved at any time from table2.

you really should look into efficient database design, often called database normalization.

to get you started:

http://databases.about.com/od/specificproducts/a/normalization.htm

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[pierrotsc]

Ok....Let me dig into that....
Thanks.
PS: the reason I am duplicating the Paper name is that I have a combobox for table 2 that let the user pick the desired paper name. When that is done, it should populate table 1 that has all the project info. Does that make sense?

 

[whosrdaddy]

yes it makes sense.

this means you only need PaperID in table1 (which is only a link to table2)

when you want to show your project details, you can lookup all needed data:

SELECT t1.Date, t1.ProjectName, t2.PaperName 
 FROM table1 t1
  INNER JOIN table2 t2 ON t2.PaperID = t1.PaperID

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[DjangMan]

Just to be fair - de-normalizing can have reporting benefits. In this case, however, I would follow Dad's advice and normalize.