SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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 cast 
    INNER JOIN users 
    ON users.user_id = cast.cast_user_id
    INNER JOIN positions
    ON positions.pos_id = cast.cast_pos_id 
    WHERE cast.cast_video_id=1

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks!!!! that works perfectly. I can't seem to get my head around all of the joins

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by aaron4osu View Post
    thanks!!!! that works perfectly. I can't seem to get my head around all of the joins
    I just translated in sql what you said in words in the beginning of your post:
    I want pull all records from the cast table where cast.cast_video_id=1.
    Code:
    SELECT 
        ...
    FROM cast 
    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
    So join the users table
    Code:
    SELECT 
        users.first_name
      , users.last_name
      , users.city
      , users.state
      , users.user_pic_path
    FROM cast 
    INNER JOIN users 
    ON users.user_id = cast.cast_user_id
    WHERE cast.cast_video_id=1
    and their position name which is in the position table.
    And finally join the position table
    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 cast 
    INNER JOIN users 
    ON users.user_id = cast.cast_user_id
    INNER JOIN positions
    ON positions.pos_id = cast.cast_pos_id 
    WHERE cast.cast_video_id=1

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another question

    Thanks again for explaining it. Your explanation allowed me to create most of the other queries on my own. I do have another one I'm having problems with. I have tried several things which haven't worked.


    I'm trying to list all cast members linked to a specific cast member. To make it simpler to explain I broke it down into several queries (which work when run separately). I think I need to create a nested or sub query.

    The first query lists all of the fields I want to display. Right now I have it card coded to list the users associated with one video id, but I want it to list every video and project cast member.


    Code:
    SELECT
      users.user_pic_path, 
      users.first_name, 
      users.last_name, 
      users.city, 
      users.state,
      positions.pos_name
    FROM users 
    INNER JOIN cast 
    ON cast.cast_user_id = users.user_id
    INNER JOIN positions
    ON positions.pos_id = cast.cast_pos_id 
    WHERE cast.cast_video_id = 1;
    I need to replace the '=1' in the where statement with the values returned from these two queries:


    Code:
    SELECT cast_video_id FROM cast
    WHERE cast_user_id=1 AND cast_video_id IS NOT NULL;
    Code:
    SELECT cast_proj_id FROM cast
    WHERE cast_user_id=1 AND cast_proj_id IS NOT NULL;
    * I have to use IS NOT NULL because the cast tables stores both videos and projects which are each seperate. So a video will have a null project id and vise versa

    So something like this:
    Code:
    SELECT
      users.user_pic_path, 
      users.first_name, 
      users.last_name, 
      users.city, 
      users.state,
      positions.pos_name
    FROM users 
    INNER JOIN cast 
    ON cast.cast_user_id = users.user_id
    INNER JOIN positions
    ON positions.pos_id = cast.cast_pos_id 
    WHERE cast.cast_video_id = 
    	(SELECT cast_video_id FROM cast
    	WHERE cast_user_id=1 AND cast_video_id IS NOT NULl)
    OR
    cast.cast_proj_id = 
    	(SELECT cast_proj_id FROM cast
    	WHERE cast_user_id=1 AND cast_proj_id IS NOT NULL);
    If possible, I don't want to list duplicated or the original cast_user_id that is in the two nested queries.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    That doesn't look too bad. Change = in IN
    Code:
    WHERE cast.cast_video_id IN 
    	(SELECT cast_video_id FROM cast
    	WHERE cast_user_id=1 AND cast_video_id IS NOT NULl)
    OR
    cast.cast_proj_id IN 
    	(SELECT cast_proj_id FROM cast
    	WHERE cast_user_id=1 AND cast_proj_id IS NOT NULL)
    You can prevent duplicates by using SELECT DISTINCT

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again!!! Your first posts made it much easier.

    One last question on this query. How do I not include the user the search is based off of (in this instance cast_user_id=1). I want to list all of the people associated with this user, but I don't want the user listed as associated with themselves. If that makes sense.

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,265
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by aaron4osu View Post
    One last question on this query. How do I not include the user the search is based off of (in this instance cast_user_id=1). I want to list all of the people associated with this user, but I don't want the user listed as associated with themselves. If that makes sense.
    It's a two step process as you'll need to be mixing and and OR conditions.

    First, enclose the entire existing selection criteria in parenthesis - in otherwords, put ( before the cast.cast_video_id IN and a ) before the semi-colon. This will combine the existing criteria into one condition.

    Next add the following right after the where : users.user_id != 1 AND

    So you're essentially saying "Show me all of the cast except for user #1 who appeared in either the projects or the videos that user#1 was involved in."
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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