Jump to content


Photo

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

query join max

  • Please log in to reply
6 replies to this topic

#1 bsamson

bsamson

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 19 November 2013 - 04:00 PM

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 - 04:05 PM.


#2 dalecosp

dalecosp

    Advanced Member

  • Members
  • PipPipPip
  • 306 posts
  • LocationJoplin, MO

Posted 19 November 2013 - 04:19 PM

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 )

Edited by dalecosp, 19 November 2013 - 04:20 PM.

"God doesn't play dice" --- Albert Einstein
"Perl is hardly a paragon of beautiful syntax." --- Weedpacket

#3 bsamson

bsamson

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 19 November 2013 - 04:22 PM

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.



#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,170 posts
  • LocationCheshire, UK

Posted 19 November 2013 - 04:32 PM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 bsamson

bsamson

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 19 November 2013 - 04:43 PM

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.



#6 bsamson

bsamson

    Advanced Member

  • Members
  • PipPipPip
  • 147 posts

Posted 19 November 2013 - 09:31 PM

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?



#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,170 posts
  • LocationCheshire, UK

Posted 20 November 2013 - 03:04 AM

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.


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com