Query returning single rows from one to many joins

[Jocsta]

At the moment, we have a system for displaying formatted results. As this is just a "picker" list, which displays a set of "user defined" details about a record, and on clicking that row - the full record details are displayed. The list is meant to be *indicative* of the data contained, not truthful (if you get my meaning)
Unfortunately as this display is user configurable the user can choose something stupid and kinda complicate the whole thing - e.g. the list is a person picker, and they choose to display "skills" as one of the columns. This would mean the person showing in the list more than once (multiply this by other multi occuring columns and .... well you know where thats going.

At the moment, we have a java method that takes this resultset, and works through it ignoring and id's its already seen in the result (basically a "distinct" - but ONLY working on the first column).

// Reset count to 0, and process again for number of rows to return.
currentRow=1;
while ( rsExecuteQuery.next() ){

if (currentRow <= this.getMaxRows())
{
entityID = rsExecuteQuery.getLong(1); //"ENTITY_ID");
if (uniqueEntityIDSet.add(new Long(entityID)))
{
// Wasnt already in the set - so add the row.
row = new ArrayList();
row.add(new Long(entityID));
for (int x = 1; x < colCount; x++)
{
row.add(rsExecuteQuery.getObject(x + 1));
}
resultSet.add(row);
currentRow++;
}
} else {
break;
}
}

It works, but it aint pretty.....
I have been looking for ways to speed this up - trying to offload more of the work to the DB.
I tried recreating it in stored procedures, but didnt get too far with my limited knowlege (further complicated by having to support any database, but I just couldnt get anything working like above).

I have managed to offload the work to the query under SQLServer2005 (and I think I can work it a similar way on Oracle but havent tried yet).

select top 10 * from (
SELECT "ROOT"."ENTITY_ID" AS "ENTITY_ID"
,"LK_ENTITY_ROLE"."DEFAULT_VALUE" AS "DEFAULT_VALUE", "ROOT"."HIGHLIGHT" AS "HIGHLIGHT", "ROOT"."WEIGHTING" AS "WEIGHTING", "ROOT"."AVAILABILITY" AS "AVAILABILITY"
-- ... more columns here.
,row_number() OVER (partition by "ROOT"."ENTITY_ID" ORDER BY "LK_ENTITY_ROLE"."DEFAULT_VALUE" DESC) as filter

FROM "SEARCH_RESULTS" "ROOT"
LEFT OUTER JOIN "LK_ENTITY_ROLE" "LK_ENTITY_ROLE" ON "ROOT"."ENTITY_ID"="LK_ENTITY_ROLE"."ENTITY_ID"
LEFT OUTER JOIN "PROP_SKILLS" "PROP_SKILLS" ON "ROOT"."ENTITY_ID"="PROP_SKILLS"."REFERENCE"
WHERE "ROOT"."SEARCH_ID"=6888502
-- ... more conditions here
)ZZ
where ZZ.filter=1
ORDER BY 2 DESC


It works, but aint all that quick, and wont help me on SqlServer2000 (sigh), so does anyone have any cool ideas?
Im sure this must be a fairly common problem - showing preview data - but im fast approaching breakdown, im just a poor programmer with no database training and a company too tight to get any...

Thanks for listening

<< JOC >>

 

[r937]

a programmer with no database training and a company too tight to get any...

... and yet the company is developing a product that has to work on several different database platforms

what's wrong with this picture?



another idea: for each one-to-many relationship, use a MAX correlated subquery to pick the highest or latest from amongst the many

r937.com | rudy.ca

 

[Jocsta]

ooohhhhhhh theres PLENTY wrong with that picture. Most our dev is outsourced - I just have to try and make it work well when we get it back from them. Right from the off I have been begging for a DBA/db developer - but hey, that would reduce the all important budget for shiny new BMW's for the sales monkeys.... but dont get me started on that

I did try the MAX subquery approach:-

SELECT top 200 ROOT.ENTITY_ID AS ENTITY_ID
FROM "SEARCH_RESULTS" "ROOT"
LEFT OUTER JOIN "PROP_PERSON_GEN" "PROP_PERSON_GEN" ON "ROOT"."ENTITY_ID"="PROP_PERSON_GEN"."REFERENCE"
LEFT OUTER JOIN "PROP_ADDRESS" "PROP_ADDRESS" ON "ROOT"."ENTITY_ID"="PROP_ADDRESS"."REFERENCE"
and (PROP_ADDRESS.BISUNIQUEID = (select top 1 BISUNIQUEID from PROP_ADDRESS WHERE REFERENCE=ROOT.ENTITY_ID))
LEFT OUTER JOIN LK_ENTITY_ROLE LK ON ROOT.ENTITY_ID = LK.ENTITY_ID
and (LK.ROLE_ID = (select top 1 ROLE_ID from LK_ENTITY_ROLE WHERE ENTITY_ID=ROOT.ENTITY_ID))
WHERE "ROOT"."SEARCH_ID"= 9009003
ORDER BY PROP_PERSON_GEN.FIRST_NAME, PROP_PERSON_GEN.LAST_NAME, ROOT.ENTITY_ID

as I know which tables are one to many - but couldnt get that to work on Oracle (I know thats not this forum but it only seems to allow one level of correlation, and you need 2 to emulate a MAX using rownum) but I was hoping there was something simple(ish) I missed/didnt yet know.

 

[r937]

oh my $deity, could your sql be any harder to read

you will not get "SELECT TOP NNN" to work on oracle, so you need something else for that

luckily, MAX() works quite nicely instead of "SELECT TOP 1" ;-)

SELECT sr.ENTITY_ID 
  FROM SEARCH_RESULTS as sr
LEFT OUTER 
  JOIN PROP_PERSON_GEN as ppg
    ON ppg.REFERENCE = sr.ENTITY_ID 
LEFT OUTER 
  JOIN PROP_ADDRESS as pa
    ON pa.REFERENCE = sr.ENTITY_ID
   AND pa.BISUNIQUEID = 
       ( select MAX(BISUNIQUEID)
           from PROP_ADDRESS 
          WHERE REFERENCE = sr.ENTITY_ID )
LEFT OUTER 
  JOIN LK_ENTITY_ROLE as LK 
    ON LK.ENTITY_ID = sr.ENTITY_ID
   AND LK.ROLE_ID = 
       ( select MAX(ROLE_ID)
           from LK_ENTITY_ROLE 
          WHERE ENTITY_ID = sr.ENTITY_ID )
 WHERE sr.SEARCH_ID = 9009003 
ORDER 
    BY ppg.FIRST_NAME
     , ppg.LAST_NAME
     , sr.ENTITY_ID

r937.com | rudy.ca

 

[Jocsta]

ERROR - $deity not found or out of range (sorry couldnt resist)

Appologies for the messy sql, its pretty much a dump of the code output with a bit of indenting - I should have cleaned it up a bit more.
I did try both ways in Oracle and hit other problems (not being able to outer join to a sub query, did find ways round that - but thats for another forum), but for Oracle I can use the ROW_NUMBER() technique.

Its one of them nasty triangles, 3 versions needed and things that only work (or work well) on 2 of the 3. If only we could drop SQLServer2000 . . . sigh

Thanks for you help tho, very much appreciated

 

[SQLSister]

Could you try having your GUI id which type of db it is querying and then run the appropriate code for that database rather than trying to make one size fits all code?

Questions about posting. See faq183-874