SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with multi-table group maximum query

    Can anyone help me to merge these queries?

    I have been looping through the first result set to get the latest comment, but this has now become too slow.

    Code:
    Query 1:
    
    SELECT invoice.invoice_number AS invoice
         , invoice.job_number AS job
         , invoice.amount AS amount
         , TO_DAYS( NOW() ) - TO_DAYS( invoice.invoice_date ) AS age
         , DATE_FORMAT( invoice.invoice_date, '%d/%m/%Y' ) AS invoice_date
         , user.display_name AS pm
         , job.client AS client
         , invoice_status.status AS status
         , invoice.office AS office
         , job.project as project
         , account_history_item.description AS type
      FROM invoice
    INNER
      JOIN job
        ON invoice.job_number
         = job.job_number
    INNER
      JOIN user
        ON user.employee_number
         = job.project_manager
    INNER
      JOIN invoice_status
        ON invoice.invoice_number
         = invoice_status.invoice_number
       AND invoice.office = invoice_status.office
    LEFT OUTER
      JOIN account_history_item
        ON invoice.invoice_number
         = account_history_item.document_number
       AND invoice.office
         = account_history_item.office
     WHERE invoice.office = '784'
    ORDER
        BY invoice.job_number
    
    Query 2:
    
    SELECT comment
         , DATE_FORMAT( comment_date, '%d/%m/%Y' ) AS comment_date
      FROM invoice_comment
     WHERE invoice_number = '*** invoice from above result ***'
       AND office = '*** office from above result ***'
    ORDER
        BY date DESC
     LIMIT 1
    I presume this is a group-wise maximum query (for the comment date), but I can't figure it out.

    The invoice_comment table joins to the invoice table using invoice_number and office as a composite key.

    Many thanks in advance,

    Matt.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first of all, i just wanted to say how beautiful your code looks!

    you're the only other person i know that indents the same way i do

    the following is not guaranteed to run faster, but it just might

    it uses the principle that in order to do the equivalent of a correlated subselect, you need a self-join of the comment table, and this requires grouping
    Code:
    SELECT invoice.invoice_number AS invoice
         , invoice.job_number AS job
         , invoice.amount AS amount
         , TO_DAYS( NOW() ) - TO_DAYS( invoice.invoice_date ) AS age
         , DATE_FORMAT( invoice.invoice_date, '%d/%m/%Y' ) 
                 AS invoice_date
         , user.display_name AS pm
         , job.client AS client
         , invoice_status.status AS status
         , invoice.office AS office
         , job.project as project
         , account_history_item.description AS type
         , invoice_comment.comment
         , DATE_FORMAT( invoice_comment.comment_date, '%d/%m/%Y' ) 
                 AS comment_date
      FROM invoice
    INNER
      JOIN job
        ON invoice.job_number
         = job.job_number
    INNER
      JOIN user
        ON user.employee_number
         = job.project_manager
    INNER
      JOIN invoice_status
        ON invoice.invoice_number
         = invoice_status.invoice_number
       AND invoice.office = invoice_status.office
    LEFT OUTER
      JOIN account_history_item
        ON invoice.invoice_number
         = account_history_item.document_number
       AND invoice.office
         = account_history_item.office
    left outer
      join invoice_comment
        on invoice.invoice_number
         = invoice_comment.invoice_number 
       AND invoice.office
         = invoice_comment.office 
    left outer
      join invoice_comment            as invoice_comment2
        on invoice.invoice_number
         = invoice_comment2.invoice_number 
       AND invoice.office
         = invoice_comment2.office      
     WHERE invoice.office = '784'
    group
        by invoice.invoice_number 
         , invoice.job_number 
         , invoice.amount 
         , TO_DAYS( invoice.invoice_date ) 
         , DATE_FORMAT( invoice.invoice_date, '%d/%m/%Y' ) 
         , user.display_name 
         , job.client 
         , invoice_status.status 
         , invoice.office 
         , job.project 
         , account_history_item.description 
         , invoice_comment.comment
         , DATE_FORMAT( invoice_comment.comment_date, '%d/%m/%Y' ) 
    having invoice_comment.comment_date
         = max(invoice_comment2.comment_date)
    ORDER
        BY invoice.job_number
    let me know how that works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    I was hoping you might spot this thread.

    Glad you approve of the code layout. I used to use a 4 spaces indent layout, like other programming, but I had an overhaul of some of my coding styles a while back, and yours won the day for SQL.

    The code was pretty close. I had to add invoice_comment.invoice_date for the field list as I got an error stating that it didn't exist in the having clause.

    But...

    (and there's always a but, isn't there? )

    It's cutting out invoices that don't have comments.

    And it is faster. Down from 30+ seconds with a loop to 4-5 seconds.

    Matt.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mincer
    It's cutting out invoices that don't have comments.
    try this --
    Code:
    having invoice_comment.comment_date
         = max(invoice_comment2.comment_date)
        or count(invoice_comment2.invoice_number) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •