Two tables, pull last record of table B that references table A

[bsamson]

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 November 19, 2013 by bsamson

[dalecosp]

Can't you use "soldOn", then? If so, this could be as simple as ORDER BY soldOn DESC LIMIT 1, right?

 

(Forgive if I'm being thick ... my native language isn't SQL )

Edited November 19, 2013 by dalecosp

[bsamson]

Can't you use "soldOn", then? If so, this could be as simple as ORDER BY soldOn DESC LIMIT 1, right?

 

(Forgive if I'm being thick ... my native language isn't SQL )

 

I appreciate the help. Sadly as it's not that easy. The sapImport table contains customer information while conStatus simply tracks interactions with the customers from sapImport. So, in what I need here the soldOn row isn't used.

[Barand]

Have you got test data dumps for those 2 tables that will illustrate your problem?

[bsamson]

Have you got test data dumps for those 2 tables that will illustrate your problem?

 

Ah, I was hoping you'd be around to help with this one!!! 

 

If there is only one reference in the conStatus table to a customer in the sapImport table - it works as expected.

 

However, if there is more than one reference in the conStatus to a customer record in the sapImport table - it returns zero rows.

[bsamson]

Essentially, I am trying to condense what can be achieved in nested while loops ... I believe the following loop will achieve what i'm looking for:

 

$qry = "SELECT * FROM sapImport JOIN conStatus ON sapImport.id = conStatus.record WHERE store='3' AND conStatus.statusCode < '998'";
$res = mysql_query($qry);


while ($row = mysql_fetch_array($res))
{
$idNo = $row['id'];
$customer = $row['customer'];
$invoiceNo= $row['soldOn'];
$ptn = $row['ptn'];
$recID = $row['record'];


$query = "SELECT * FROM conStatus WHERE record = '$recID' ORDER BY id DESC Limit 1";
$result = mysql_query($query);


while ($row = mysql_fetch_array($result))
{
$record = $row['record'];
$stCode = $row['statusCode'];
}
}

Any suggestions?

[Barand]

Have you got test data dumps for those 2 tables that will illustrate your problem?

 

I'll rephrase that^^

 

Let me have test data dumps for those 2 tables that will illustrate your problem then I can work on the query.