help with query joining 3 tables

[aaron4osu]

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

[guido2004]

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

[aaron4osu]

thanks!!!! that works perfectly. I can't seem to get my head around all of the joins

[guido2004]

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.

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

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

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

[aaron4osu]

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.

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:

SELECT cast_video_id FROM cast
WHERE cast_user_id=1 AND cast_video_id IS NOT NULL;

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:

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.

[guido2004]

That doesn't look too bad. Change = in IN

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

[aaron4osu]

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.

[DaveMaxwell]

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."