UnKnown UnKnown - 18 days ago 6
MySQL Question

How to get details using In Operator

I have got two tables video_details and video_tag .
I am trying to get the video_details data for which belong to particualr tag id

This is my table structure

CREATE TABLE IF NOT EXISTS `video_details` (
`video_id` int(6) NOT NULL auto_increment COMMENT 'Auto Generated key',
`video_name` varchar(50) default NULL,
`video_file` varchar(100) default NULL,
`how_to_video` varchar(100) default NULL COMMENT 'information video',
`video_desc` varchar(50) default NULL,
`video_created_date` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`video_status` tinyint(1) default NULL COMMENT '0-->video will not be displayed.,1-->video will be displayed ',
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB AUTO_INCREMENT=382 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `video_tag` (
`video_id` int(6) default NULL,
`tag_id` int(11) default NULL,
KEY `FK__tag_details` (`tag_id`),
KEY `FK__video_details` (`video_id`),
CONSTRAINT `FK__video_details` FOREIGN KEY (`video_id`) REFERENCES `video_details` (`video_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


corresponding sql fiddle

http://sqlfiddle.com/#!9/74b210

I have tried it this way

select vt.video_id , vd.video_name from video_tag vt , video_details vd
where vt.tag_id in (1,2)

Answer

simply use join

SELECT vt.video_id , vd.video_name 
FROM video_tag vt 
LEFT JOIN video_details vd on vd.video_id = vt.video_id
WHERE vt.tag_id in (1,2)
Comments