INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Trying to find MAX from join of two tables

Trying to find MAX from join of two tables

(OP)
I'm sure this is easy but I've been banging my head on it for a while and can't come up with the proper solution,

I have two tables maintaining transaction records for items,

whscosthdr [whh_cdate,whh_username,whh_trans_no,whh_tsource,whh_from_loc,whh_to_loc,whh_pct_incr]

whscostdtl [whd_trans_no,whd_tsource,whd_from_loc,whd_style,whd_colour,whd_width,whd_sze,whd_qty,whd_tcost]

I need to find the the cost of the last transaction by item form the tables, so basically each items MAX transaction date from the whhcosthdr table and the item/cost information from the whscostdtl. The tables are joined on the related columns (primary key),

whh_trans_no=whd_trans_no
whh_tsource=whd_tsource
whh_from_loc=whd_from_loc

There are 3542 unique items (based on sytle+colour+width+size) in the whscostdtl table so really this should be the maximum records I'm getting back, along with the additional fields needed.

Here's what I've been able to come up with but it takes forever to run and returns over 9000 rows so I'm definitely missing something - just not really sure what :)

CODE --> mysql

SELECT whscostdtl.whd_style
  , whscostdtl.whd_colour
  , whscostdtl.whd_width
  , whscostdtl.whd_sze
  , whscostdtl.whd_tcost AS whd_lastcost
  , whscosthdr.whh_cdate
  , max_date.*
FROM whscosthdr
JOIN whscostdtl
  ON whscostdtl.whd_trans_no = whscosthdr.whh_trans_no
 AND whscostdtl.whd_tsource = whscosthdr.whh_tsource
 AND whscostdtl.whd_from_loc = whscosthdr.whh_from_loc

JOIN (

SELECT MAX(whscosthdr.whh_cdate) AS max_date
  , whd_from_loc
  , whd_style
  , whd_colour
  , whd_width
  , whd_sze
      FROM whscosthdr
      JOIN whscostdtl
        ON whscostdtl.whd_trans_no = whscosthdr.whh_trans_no
       AND whscostdtl.whd_tsource = whscosthdr.whh_tsource
       AND whscostdtl.whd_from_loc = whscosthdr.whh_from_loc
     WHERE whscosthdr.whh_from_loc='98'
     GROUP BY  whd_style, whd_style, whd_colour, whd_width, whd_sze
     
     ) AS max_date
     ON max_date.whd_from_loc=whscosthdr.whh_from_loc
    AND max_date.max_date=whscosthdr.whh_cdate
    AND max_date.whd_style=whscostdtl.whd_style
    AND max_date.whd_colour=whscostdtl.whd_colour
    AND max_date.whd_width=whscostdtl.whd_width
    AND max_date.whd_sze=whscostdtl.whd_sze 

Any suggestions? Somehow I feel I'm making this problem far more complicated than it should be but it's just not clicking for me.

Thanks!

RE: Trying to find MAX from join of two tables

(OP)
I managed to solve my problem after, just needed to take it a step further, and with an extra index added is running quick and smooth.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close