I’m trying to build a query that joins 3 tables but I can’t seem to figure it out.
I want pull all records from the cast table where cast.cast_video_id=1. It should return several users and I want to list their info which is listed in the users table and their position name which is in the position table.
here is what I have thus far which returns this error:
ERROR: Please separate SQL statements with the Statement Delimiter Preference value - currently ; - when using Execute All
SELECT users.first_name, users.last_name, users.city, users.state, cast.cast_pos_id, cast.cast_video_id, positions.pos_name, cast.cast_user_id, users.user_pic_path
FROM users INNER JOIN (positions INNER JOIN [cast] ON positions.pos_id = cast.cast_pos_id) ON users.user_id = cast.cast_user_id
where cast.cast_video_id=1;
here are my mysql tables used in query
CREATE TABLE `cast` (
`cast_id` int(11) NOT NULL AUTO_INCREMENT,
`cast_user_id` int(11) DEFAULT NULL,
`cast_video_id` int(11) DEFAULT NULL,
`cast_pos_id` int(11) DEFAULT NULL,
`cast_detail` varchar(200) DEFAULT NULL,
PRIMARY KEY (`cast_id`),
FOREIGN KEY(cast_user_id) REFERENCES users(user_id),
FOREIGN KEY(cast_video_id) REFERENCES videos(vid_id),
FOREIGN KEY(cast_pos_id) REFERENCES positions(pos_id)
);
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`first_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`city` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`state` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`zip` int(5) DEFAULT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`user_pic_path` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`user_id`),
FOREIGN KEY(user_pos_id) REFERENCES positions(pos_id)
);
CREATE TABLE `positions` (
`pos_id` int(11) NOT NULL AUTO_INCREMENT,
`pos_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`pos_desc` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`pos_id`)
);