I thought I had this issue figured out last night with the help from this forum, but turns out it still isn't working as expected.
I have two tables:
Table: sapImport id invoiceNo invoicedAtID invoicedAtName soldOn soldBy customer 1 CICIN123 3 Cicero 1384832632 brian john smith 2 DESTIN12 5 Destiny 1384832632 brian Henry Will 3 VESTIN32 3 Cicero 1384832632 jason Peter Jenn
Table: conStatus id store record statusCode 34 3 1 0 35 5 3 0 39 3 1 15
I do have more rows than this, but i'm just trying to give an idea ...
I currently have the following query:
SELECT * FROM sapImport si INNER JOIN conStatus cs ON si.id = cs.record INNER JOIN ( SELECT record, MAX(id) as id FROM conStatus GROUP BY record ) mx ON cs.record = mx.record AND cs.record = mx.id WHERE cs.store='$storeID' AND cs.statusCode < '998'
Basically, the table sapImport contains customer information and the conStatus table contains all interactions with a record in the sapImport table. The 'id' row in sapImport is the primary key and is auto-incremented. The 'record' row in conStatus references the 'id' row from sapImport. I need a query that pulls the LAST record (from conStatus) for a particular customer (from sapImport) where the store=x and the statusCode is less than 998. I should only get ONE result set with this.
Any help will be GREATLY appreciated! Thanks in advance!
Edited by bsamson, 19 November 2013 - 09:05 PM.