Page 1 of 1

Don't have my query just right yet

Posted: Mon Jan 28, 2008 4:04 pm
by micknc
I have been working a search.php and I am almost there. I am trying to get data from two tables. SOHEAD is the main table and then I need a little info from TRACK. Here is what I have.

Code: Select all

 
$query = "SELECT SOHEAD.CUST_ID, SOHEAD.SO_NO, SOHEAD.SHIP_ID, SOHEAD.CANC_DATE, SOHEAD.`STATUS`, SOHEAD.PO_NO, SOHEAD.REQRD_DATE, TRACK.TRACK_SO_NO, TRACK.TRACK_STATUS, TRACK.TRACK_DATE, TRACK.TRACK_TIME, TRACK.TRACK_NOTES, TRACK.TRACK_NUMBER, TRACK.TRACK_PO_NO, TRACK.TRACK_CUST_ID FROM SOHEAD Left Join TRACK ON SOHEAD.SO_NO = TRACK.TRACK_SO_NO WHERE SOHEAD.CUST_ID ='$id' AND STATUS='S' ORDER BY SO_NO DESC";
 
For examples sake lets say I have 3 entries for 1 for CUST_ID in SOHEAD with the SO_NO 1, 2, 3,
I am trying to pull TRACK_SO_NO 1, 2, and 3 and that is working fine.

The question I am not able to resolve is that all I want to produce is the last entry for TRACK_SO_NO. It passes through several stations 1-20. So if 14 is the last one I want to print 14 but not 1-13. Specifically TRACK_STATUS is the field I am trying to limit.

I have thought about breaking it into two queries but then the water gets muddy.
Any ideas?

Re: Don't have my query just right yet

Posted: Tue Jan 29, 2008 12:11 am
by novice4eva
I don't know if i got the question right but hopefully this might help you to fetch a single row from the record set(this is the exact extract from underground-php-oracle-manual.pdf)..

Code: Select all

 
select *
from ( select a.*, rownum as rnum
from (YOUR_QUERY_GOES_HERE -- including the order by) a
where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
 
/* MIN_ROWS is the row number of first row and MAX_ROWS is the row number of last row
to return. In PHP you might do this: 
$mystmt = "select city from locations order by city";
$minrow = 4; // row number of first row to return
$maxrow = 8; // row number of last row to return
$pagesql = "select *
from ( select a.*, rownum as rnum
from ( $mystmt ) a
where rownum <= :maxrow)
where rnum >= :minrow";
*/
 

Re: Don't have my query just right yet

Posted: Tue Jan 29, 2008 6:06 am
by Inkyskin
If I read your post correctly, you are getting the right results, but you just want one at a time. If thats the case, just append limit to the end, like so:

[sql]SELECT SOHEAD.CUST_ID, SOHEAD.SO_NO, SOHEAD.SHIP_ID, SOHEAD.CANC_DATE, SOHEAD.`STATUS`, SOHEAD.PO_NO, SOHEAD.REQRD_DATE, TRACK.TRACK_SO_NO, TRACK.TRACK_STATUS, TRACK.TRACK_DATE, TRACK.TRACK_TIME, TRACK.TRACK_NOTES, TRACK.TRACK_NUMBER, TRACK.TRACK_PO_NO, TRACK.TRACK_CUST_ID FROM SOHEAD LEFT JOIN TRACK ON SOHEAD.SO_NO = TRACK.TRACK_SO_NO WHERE SOHEAD.CUST_ID ='$id' AND STATUS='S' ORDER BY SO_NO DESC LIMIT 0, 1[/sql]

Re: Don't have my query just right yet

Posted: Tue Jan 29, 2008 10:41 pm
by micknc
Wouldn't that just give me one result? I want the array but from TRACK I just want one result. Let me see if I can explain it better. Lets say this is was my first table:
  • Table 1
    SO, QTY, DESCR
    1, 50, planes
    1, 50, trains
    1, 50, bikes

    Table 2
    SO, Status
    1, Entered
    1, Filed
    1, Sorted in warehouse
    1, Shipped
So my query needs to return all the entries from Table 1 so that I can reproduce their order but I only want the last one from Table 2 so that I would only show current status.

Maybe that makes it a little more clear.
Thanks again for the help on this one. I am so close I can taste it.

Re: Don't have my query just right yet

Posted: Fri Nov 07, 2008 2:32 am
by novice4eva
I don't know if that is possible but i am not an expert! But i do think that using function you can do it: create a function that returns the last record for the provided SNO.

Re: Don't have my query just right yet

Posted: Fri Nov 07, 2008 4:47 am
by VladSun
Your Table2 is not looking good ;) Try to change status from string to numbers (e.g. 1='Entered', 2='Filled') so the status change order is clear.
Then you may use a subselect:
[sql]...LEFT JOIN     TRACK ON         SOHEAD.SO_NO = TRACK.TRACK_SO_NO         AND        TRACK.`status` = (                                SELECT                                         max(_TRACK.`status`)                                 FROM                                         TRACK AS _TRACK                                 WHERE                                         _TRACK.TRACK_SO_NO  = TRACK.TRACK_SO_NO                                )... [/sql]