SitePoint Sponsor

User Tag List

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

    Help combing two queries

    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:
    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aaron4osu View Post
    Here are the two queries:
    i see only one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,259
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    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?
    "First make it work. Then make it better."

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do I do that?

  5. #5
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,259
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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;
    "First make it work. Then make it better."

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •