Help combing two queries

[aaron4osu]

I'm trying to combine the results of two separate queries and wasn't sure if what I want to do is possible. Here are the two queries:

SELECT 			*
				FROM projects 
 				INNER JOIN users 
 				ON users.user_id = projects.proj_user_id
 				INNER JOIN genre
 				on genre.genre_id = projects.genre_id 
 				INNER JOIN format
 				ON format.format_id = projects.format_id
 				where proj_user_id = 2 or (SELECT *
 				FROM cast 
				INNER JOIN users 
				ON users.user_id = cast.cast_user_id
				INNER JOIN positions
				ON positions.pos_id = cast.cast_pos_id 
				INNER JOIN projects
				on projects.proj_id = cast.cast_proj_id
				INNER JOIN genre
				on genre.genre_id = projects.genre_id 
				INNER JOIN format
				ON format.format_id = projects.format_id
				where cast.cast_user_id = 2);

IS this possible?

[r937]

Here are the two queries:

i see only one

[Jeff Mott]

I think there are two nested queries.

Let this be a lesson to the OP and anyone else who reads this thread: Code formatting is important! Bad formatting makes code difficult to understand.

Back to the original question... In your nested query, you're joining the projects table to the cast table. Why not instead, in your outer query, just join the cast table to the projects table?

[aaron4osu]

how do I do that?

[Jeff Mott]

SELECT 			*
				FROM projects 
 				INNER JOIN users 
 				ON users.user_id = projects.proj_user_id
 				INNER JOIN genre
 				on genre.genre_id = projects.genre_id 
 				INNER JOIN format
 				ON format.format_id = projects.format_id
				INNER JOIN cast
				ON cast.cast_user_id = projects.proj_user_id
				INNER JOIN positions
				ON positions.pos_id = cast.cast_pos_id 
 				where proj_user_id = 2;

[SpacePhoenix]

On a related note do you need every field from each table in the results set? If not then specify each required field in the result set

da_table.da_field AS alias_name

The use of the alias is needed if two or more fields in the result set have the same name. "Qualifying" the field name with the table name is not necessary if only one field has a certain name but it's a good habit to get into always doing it as it kinda makes the query self-documenting, you can see for each field in the result set, which table you're getting it from.