Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Unanswered: Please help me optimize this query

    The following query takes forever to complete and I'm not exactly an SQL guru so I was hoping some of the experts here could take a quick look at it. Can you see anything seriously wrong with it?
    Code:
    SELECT ord_workorders.*
    FROM (ord_workorders
    LEFT JOIN ord_reports r1 ON (ord_workorders.id = r1.workorderid AND r1.invoiced = 1))
    LEFT JOIN ord_reports r2 ON (ord_workorders.id = r2.workorderid AND r2.invoiced <> 1)
    WHERE ord_workorders.completed = 1
    AND (r1.workorderid IS NULL OR r2.workorderid IS NOT NULL)
    GROUP BY ord_workorders.id ORDER BY ord_workorders.title DESC
    When I run it in phpMyAdmin I get one column full of question marks that says (when I hover over them) "You should define a primary key for this table". I've got primary keys on all tables, so what does this message mean?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, let's address the issue of your GROUP BY, which is invalid (on account of your use of the dreaded, evil "select star")

    you want one result per ord_workorders.id, so presumably, you want distinct rows from the ord_workorders table, based on whether the row satisfies your LEFT JOINs and WHERE conditions

    could you explain in words what those LEFT JOINs are supposed to do?

    it looks to me like you want completed workorders that
    - do not have an invoiced row in the reports table
    OR
    - that do have a reports row for any other invoice status

    i'm not sure i understand this logic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Posts
    43
    I'll try my best to explain. There are work orders (ord_workorders) and, in another table (ord_reports) there are "reports" that belong to a work order. The results of this query is supposed to show completed work orders.

    A work order is considered completed when the "completed" column is "1", but if the work order contains one or more reports, and all of these reports are "invoiced", it should not be included in the result, because those work orders are called "invoiced work orders" and they are to be displayed on another page.

    To sum up, I'm looking for work orders (ord_workorders) that:
    1. have been set as "completed"
    2. have got at least one report (ord_reports) that is not set as "invoiced"

    Thanks very much in advance for anything that could help me!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does this work for you --
    Code:
    SELECT *
      FROM ord_workorders
     WHERE completed = 1
       AND EXISTS
           ( select *
               from ord_reports  
              where workorderid = ord_workorders.id 
                AND invoiced <> 1 )
    ORDER 
        BY ord_workorders.title DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Posts
    43
    I get this:
    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS ( select * from ord_reports

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what do you get for this query:
    Code:
    select version() as v
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    AND primarykey EXISTS in (SELECT foreignkey ...?

    *shrugs*
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2003
    Posts
    43
    Hi again, I've been away for a while, sorry for the late response while you're trying to help me out with this.

    The MySQL version on the server is (unfortunately) 4.0.20.

    georgev, sorry I'm not sure what to make of your post.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's what i figured

    well, i think you should upgrade to 4.1

    you'd be able to use subqueries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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