Inserting into a join table

[millicent]

MySQL version 5.1.41. I'm going through Head First SQL and I learned to make a many to many relationship like so.

 

CREATE TABLE `files` (
  `file_id` int(11) NOT NULL AUTO_INCREMENT,
`file_name` varchar(260) NOT NULL,
  PRIMARY KEY (`file_id`),
) ENGINE=InnoDB

CREATE TABLE `files_tags` (
`file_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`file_id`,`tag_id`),
KEY `file_id` (`file_id`),
KEY `tag_id` (`tag_id`),
CONSTRAINT `file_id` FOREIGN KEY (`file_id`) REFERENCES `files` (`file_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `tags` (
`tag_id` int(11) NOT NULL AUTO_INCREMENT,
`tag_name` varchar(30) NOT NULL,
PRIMARY KEY (`tag_id`),
) ENGINE=InnoDB

 

What I can't find is, now how do I add a tag to a file? I figured it had something to do with subqueries, but this is as far as I got:

 

INSERT INTO files_tags (file_id, tag_id)
VALUES ((SELECT file_id FROM files WHERE file_name = 'somefile'), (er....some kind of thing that inserts if the tag doesn't exist and then returns the tag_id))

 

Any help please?

[fenway]

I don't follow.

[millicent]

Well, thank you for reading my question.

 

I have the files table and the tags table with a join table in between them to connect them.

 

files

file_name

file_id

 

tags

tag_name

tag_id

 

files_tags

file_id

tag_id

 

Head First SQL says this is how you do a many-to-many relationship. Except how do I add a tag to a file now? The tags and files are in separate tables. So if I have a file in files called sunset.jpg and I want to add the tag 'sunset' to it, how can I do that when there are three tables involved?

 

If it was all in one table, I could do

UPDATE files SET tag='sunset' WHERE file_name='sunset.jpg'

But how do I do that query when the data is spread across three tables?

[fenway]

Ah, yes -- with JOINs -- are you familiar with these?