Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Cool Unanswered: Multi Table Query

    Hi! I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY, but I'm unsure of how to fix it. Can my query be done this way? If not, how? Thanks!


    SELECT
    quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
    machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
    quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
    FROM quote
    LEFT JOIN machining_operations
    ON machining_operations.quote_num = quote.quote_id
    LEFT JOIN parts
    ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
    GROUP BY quote.quote_id

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So....it does not work, now. Does that mean:
    1. It now throws a syntax error
    2. It returns no results
    3. It returns wrong results
    4. It returns unexpected results
    5. Other

  3. #3
    Join Date
    Jul 2012
    Posts
    4
    2. It returns no results

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    And if you remove the group by clause?

  5. #5
    Join Date
    Jul 2012
    Posts
    4
    Thank you!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mprosnitz View Post
    SELECT
    quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
    machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
    quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
    FROM ...
    GROUP BY quote.quote_id
    i thought microsoft sql server would reject this with a syntax error, as not all of the SELECT expressions are in the GROUP BY clause

    wut up wif dat?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2012
    Posts
    4
    That very well have been the issue, once I deleted the GROUP BY it worked.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mprosnitz View Post
    That very well have been the issue
    in which case you would've gotten

    1. It now throws a syntax error

    instead of

    2. It returns no results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Or
    2. It returns no results because of the blasted syntax error

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by papadi View Post
    Or
    2. It returns no results because of the blasted syntax error
    if that's the case, then the app's error detection capability is crap
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds as though the app successfully detected the error. It just swallowed it whole, and hid every trace of it.

Tags for this Thread

Posting Permissions

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