Link Tables with ID key

[peterclutton]

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

[michaellunsford]

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.

[fenway]

[!--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.

[peterclutton]

[!--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?

[fenway]

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.