[thelist] SQL trouble

Dan Parry dan at shaded.plus.com
Wed Nov 16 16:57:03 CST 2011


Hi Anthony

Thanks for the excellent advice! One thing, though: While the 'all status of 3' query works (and returns 2 records), leaving off the WHERE clause reports all batches have a current status of 1.  When a batch is created it gets a status of 1 then other records accrue with differing statuses

The results show that the query is selecting the correct history record (by its id) but displaying the status of a different history record

That seems to me to be really weird. I must be missing something; maybe the 14 hour day is starting to take its toll

Cheers

Dan

-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Anthony Baratta
Sent: 16 November 2011 21:48
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL trouble

Here's one possible solution (Rudy might slap me down):

(This assumes you are using auto-increment for the id - the largest id for a batch is always the current history.)

select all current status...

select batches.id, batches.uniq, max(history.id) from batches join history on batches.id = history.id group by batches.id, batches.uniq

select all status of 3...

select batches.id, batches.uniq, max(history.id) from batches join history on batches.id = history.id where history.status = 3 group by batches.id, batches.uniq

--
Anthony Baratta

"I used to think the brain was the most
  important part of my body, until I realized
  what was telling me that." - Emo Phillips

-- 

* * Please support the community that supports you.  * * http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt ! 


More information about the thelist mailing list