Most effective Query?

[irx]

Hey all,
I need help from all you genuises out here.

I have 3 tables

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`tag_id`)
)

CREATE TABLE IF NOT EXISTS `video_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `date_posted` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `video_tags` (
  `video_id` int(11) NOT NULL DEFAULT '0',
  `tag_id` int(11) NOT NULL DEFAULT '0'
)

Heres what I am trying to do in psuedo ... hope this makes sense...

$search = $_GET['search'];

SELECT 'tags.id' FROM tags WHERE tag_name = $search;
SELECT 'video_tags.video_id' FROM video_tags WHERE video_tags.tag_id = tags.tag_id;
SELECT * FROM video_posts WHERE video_post.id = video_tags.video_id;

I am trying to make it so when someone clicks on a keyword in the Tag Cloud, it will search the site for similar videos containg that tag.

I am trying not to run multiple queries.. and was wondering if there is a efficient and condensed way of doing this?

PS.. I am not the best at designing db's. If you there is a better solution to my db design, please I would be more then interested in hearing it!

Thanks all!

[Ramy Mahrous]

I am trying not to run multiple queries.. and was wondering if there is a efficient and condensed way of doing this?

Stored Procedure.

If you need help in designing database please send us a description to these tables.

I don't know what you need from this query?? do you use Microsoft SQL Server?

[Ole Raptor]

1.

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`tag_id`)
)

you do not have a field called tag_id. it is only id

2. research joins.

SELECT tags.id, video_tags.video_id, video_posts.fields from tag_name join video_tags on video_tags.video_id = tags.tag_id join video_posts.id = video_tags.id where tags.tag_name = $search