Jump to content

Link Tables with ID key


peterclutton

Recommended Posts

Hi all, any help would be much appreciated

I know that you can seperate data out into multiple tables and join themm with a unique ID field, but i dont understand the process of loading the data into both tables and haveng those keys the same.

For instance if you have client information and then attendee information of those clients who attended a course of yours, you could have two tables:

Clients:

ID Contact Address Phone

Attendees:

ID Course ClientID


And in the attendee table ClientID would be the same as the id from the clients table, so you dont have to repeat informtation in both tables. But If you have existing data, like from csvs, how do you keep those fields the same when loading the data?

Please let me know if i haven't made any sense or haven't included enough information
Link to comment
Share on other sites

Someone was kind enough to answer this question for me a while back. Hopefully I can return the favor to you.

SELECT clients.*,attendees.course,attendees.clientid FROM clients,attendees WHERE clients.id = attendees.id

I found this out the hard way: clients.* will return every field. If you call attendees.*, your duplicate named "id" field will be ambiguous and the search will fail. So, you can declare all the fields from one table, but will have to declare the fields you want in the result from the second table -- one at a time.

I'm no guru, though, so holler if this doesn't make any sense.
Link to comment
Share on other sites

[!--quoteo(post=369419:date=Apr 27 2006, 08:08 PM:name=GordonFreeman83)--][div class=\'quotetop\']QUOTE(GordonFreeman83 @ Apr 27 2006, 08:08 PM) [snapback]369419[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi all, any help would be much appreciated

I know that you can seperate data out into multiple tables and join themm with a unique ID field, but i dont understand the process of loading the data into both tables and haveng those keys the same.

For instance if you have client information and then attendee information of those clients who attended a course of yours, you could have two tables:

Clients:

ID Contact Address Phone

Attendees:

ID Course ClientID
And in the attendee table ClientID would be the same as the id from the clients table, so you dont have to repeat informtation in both tables. But If you have existing data, like from csvs, how do you keep those fields the same when loading the data?

Please let me know if i haven't made any sense or haven't included enough information
[/quote]
If I've understood correctly, you insert the client record first, and then get back the newly created ID using a call to MySQL's last_insert_id() function (or an equivalent function in your DB layer in Perl/PHP). With this ID in hand, you can easily insert the attendees record & pass the clientID from the previous query into your create statement. Voila!

Hope that makes sense.
Link to comment
Share on other sites

[!--quoteo(post=369655:date=Apr 29 2006, 04:20 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Apr 29 2006, 04:20 AM) [snapback]369655[/snapback][/div][div class=\'quotemain\'][!--quotec--]
If I've understood correctly, you insert the client record first, and then get back the newly created ID using a call to MySQL's last_insert_id() function (or an equivalent function in your DB layer in Perl/PHP). With this ID in hand, you can easily insert the attendees record & pass the clientID from the previous query into your create statement. Voila!

Hope that makes sense.
[/quote]


Thanks for the help, i think i understand what you're saying. I had things a bit mixed up. I just need to insert one table, then use the values from that id field for the corresponding id in the other.


It was just the fact that i had 2 csv's of the data already. So i was trying to figure out a way to get those fields to match. Should i manually put the ids in for those ones and then upload them as they are? Does that make sense?
Link to comment
Share on other sites

You don't have do it manually, depending on what version you have... the more recent versions allow you to add data to an existing table without specifying all the columns, so you can have an auto-increment column already. Not that it would be hard to modify the CSV either -- it's your choice. You could also add the ID after the import, too.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.