Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: help with query joining 3 tables

    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

    Code:
    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
    Code:
    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`)
    );

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •