Jump to content

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


bsamson

Recommended Posts

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
Link to comment
Share on other sites

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 :P )

 

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.

Link to comment
Share on other sites

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!!!  :happy-04:

 

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.