[Resolved] Need help with a tricky query

TruckStuff

I could use some advice on how best to accomplish this. I have a series of data with the elements seen below. I need some code (preferably a single query) that will return the each unique OID if ItemID is > 0 (see OID 8) OR the OID of the FIRST ItemID if several elements are < 0 (see OIDs 1 and 3). Example:

OID  Number  ItemID  Should Return
===  ======  ======  =============
 1     1        -1          -1
 1     2        -3             

 3     1        -2          -2
 3     2        -1       

 3     3        -1      

 6     1        -2          -2
 7     1        -1          -1
 8     1         1           1

I am writing this in PHP (obviously), so I am open to using a different query for each case. Returning the OID of positive ItemIDs is easy using DISTINCT, however if I use DISTINCT when the ItemID is < 0, then the OID is listed for both ItemID=-1 and ItemID=-3 (in the case of OID 1).

Kind of confusing, I know, I'll clarify if need be. Any help is appreciated. Thanks!

DeltaRho2K

Looking for clarification:

Do you want to return one or the other... In other words, if OID 8 didn't exist, you actually want the values for OID 1 (number 1), OID 3 (number 1), OID 6 (number 1) and OID 7 (number 1)?

If that is the case, you are probably going to have to do 2 separate queries to determine if the positive one exists (OID8), and if so, display that one... if not, then display the others.

Can't answer fully until I get a little clarification though.

Also, are you ever dealing with values of 0, or is it always positive and negative numbers?

TruckStuff

OID is the primary key of the table, so I'm more concerned about returning unique ItemIDs (each row in the example is a separate record). Each record could potentially contain any number of items, and I need to be able to generate reports that list OIDs based on the ItemIDs. The problem is that ItemIDs < 0 are "classifications" of the same ItemID, whereas ItemIDs > 0 are unique (I am not dealing with ItemIDs == 0, FYI).

Maybe I can clarify this some. Suppose I issue the query 'select distinct OID from Example where ItemID=-1'. This would return OIDs 1, 3 and 7. I need something that would only return OIDs 1 and 7, because OID 3 has an ItemID of -2 before the ItemID of -1 (I should note that the order of ItemIDs is bot determined by any type of sort; order is determined by the way it comes out of the db). If I issue 'select distinct OID from Example where ItemID=-2', I should get OIDs 3 and 6; the query 'select distinct OID from Example where ItemID=-3' shoud return nothing based on this data.

I'm thinking at this point, I may have to issue some sort of IF condition in the query to reassign certain values temporarily. Thoughts?

DeltaRho2K

I am not really in a position to try to duplicate this at this moment (I will be later on). When I have the opportunity, I will try this out to see if I can be of any help...

BTW, you said the OID is a primary key? If that's the case, then it should be a uniqe value... Do you have a separately indexed field i
in your table?

TruckStuff

I'm sorry... the primary key is the combination of OID and Number, e.g. 'alter table Example add Primary key (OID,Number)'

DeltaRho2K

Sorry, I caught that after I started creating a table to duplicate your scenario... I'm still working some numbers to simplify this as much as possible (and so far, it is not that simple ;-)

TruckStuff

I appreicate your help. I'm going to do some searching and research tonite and see if I can't come up with something that works.

tomhath

If you have mysql 4.x or most other databases you can do it with a NOT EXISTS. I''ll assume you are using mysql 3.xx so you can use a LEFT JOIN to get the effect of a NOT EXISTS:

SELECT * 
FROM otable a
LEFT JOIN otable b
ON a.OID = b.OID and a.Number > b.Number and a.ItemID < 0
WHERE b.OID is null

I'm not sure if this is what you really want. A big part of the problem is that you have two different data entities in the same table: Items and Item Classifications. You really should have a separate table for the classifications instead of playing tricks with the negative number. Tricks like that almost always come back to bite you.

TruckStuff

well I found A solution, even if its not an elegant one. :o I basically have to run two queries: one to grab all OIDs that contain a specific number <0 (e.g. '...where ItemID=-1'), then I run a second query that grabs the FIRST ItemID for each OID that is returned by the first. If the ItemID of the second query equals the ItemID of te first query, I display it, otherwise it is skipped. Here is the psuedocode:

$results = mysql_query ('select distinct OID, ItemID from Example where ItemID=-1')
while ($oid = mysql_fetch_array($results)
{
 $results2 = mysql_query ('select ItemID from Example where OID='.$oid['OID'].' and ItemID < 0 order by Number asc limit 1');
 if ($results2['ItemID'] != $oid['ItemID'])
   continue;
 echo $oid['OID'].' = '.$oid['ItemID'];
}

Thanks for the thoughts and input. 🙂