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?
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?
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
[COLOR="#FF0000"] INNER JOIN cast
ON cast.cast_user_id = projects.proj_user_id
INNER JOIN positions
ON positions.pos_id = cast.cast_pos_id
[/COLOR] where proj_user_id = 2;
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.