beginner- Joining tables
Posted: Sat Feb 05, 2005 3:04 am
Hi,
I only started to use MySQL 4 weeks ago so apologies if I’m not clear in my call for help.
Basically my question is very easy in that I simply have a table with around 5 photographer's images in it. Each of these photographers has nearly 100 shots each and I need to pop in their email address once somebody clicks on a small thumb. Now as there is only 5 photographers I wanted to have the email addresses of these in a separate table so that I didn't have to insert an email address into each individual field in the main table which would result in problems later on if some of these changed their email address for example.
I have been trying to do this in MSQL Administrator and so far have struggled to make this relationship work. I think though that this is the best way to do this (I hope) but at the moment I can't seem to get my head around assigning a foreign key from one table to the other. I keep getting a 1005 error.
I have set up 2 new tables (just to simplify things for the mo) hopefully making this question a little easier to follow.
table 1 called tblimages has 3 fields in this - image_id (pk) - image_name -
email_fk
table 2 called tblimages_email has 3 fields email_id(pk) - email_name -
email_address.
Now I thought that I could join these in that table 1 would hold the passed
data from the 3rd field email_address (table 2) by inserting the email_id
from the second table into the first table field 3 as a foreign key.
Am I wrong do you think?
Here are some images of my SQL Administrator to hopefully show where I might be going wrong. When I try to run this I get the error 1005.
Thanks very much as I’m at this for 3 days and thought that this was going to be easy.
Brian
http://www.ahamay.com/sql2.htm
I only started to use MySQL 4 weeks ago so apologies if I’m not clear in my call for help.
Basically my question is very easy in that I simply have a table with around 5 photographer's images in it. Each of these photographers has nearly 100 shots each and I need to pop in their email address once somebody clicks on a small thumb. Now as there is only 5 photographers I wanted to have the email addresses of these in a separate table so that I didn't have to insert an email address into each individual field in the main table which would result in problems later on if some of these changed their email address for example.
I have been trying to do this in MSQL Administrator and so far have struggled to make this relationship work. I think though that this is the best way to do this (I hope) but at the moment I can't seem to get my head around assigning a foreign key from one table to the other. I keep getting a 1005 error.
I have set up 2 new tables (just to simplify things for the mo) hopefully making this question a little easier to follow.
table 1 called tblimages has 3 fields in this - image_id (pk) - image_name -
email_fk
table 2 called tblimages_email has 3 fields email_id(pk) - email_name -
email_address.
Now I thought that I could join these in that table 1 would hold the passed
data from the 3rd field email_address (table 2) by inserting the email_id
from the second table into the first table field 3 as a foreign key.
Am I wrong do you think?
Here are some images of my SQL Administrator to hopefully show where I might be going wrong. When I try to run this I get the error 1005.
Thanks very much as I’m at this for 3 days and thought that this was going to be easy.
Brian
http://www.ahamay.com/sql2.htm