|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Needs help with advanced queryI am having some trouble with a query. I will try to explain the scenario and how the tables relate and what they are used for. The table- and viewdefinitions and inserts(I have only included the columns needed for the query) are located here: http://norskwebforum.no/pastebin/5988 The query I have made and that almost works is located here: http://norskwebforum.no/pastebin/5989 By the way, this is SQL Server 2000. Well, down to action. I have a table that consists of chemical products. A product can be compared with other products regarding health, fire and environment risk. The products are evaluated based on criterias and the whole riskevaluation is loged for further use. One product may be evaluated several times by several organisations. If a user searches for a product that has been evaluated then he or she should see the status from the last evaluation even if several have been conducted. The way I save the evaluation data is one table that holds a "groupID", dateCReated, conclusion, etc... Then in a one-too-many relation I then have a table with a FK too the "groupID" that holds information about which products were evaluated and what score they got. Now what I need, and causes me trouble is the following: When searching for (or showing a product), I want a distinct resultset where a product only occurs once, despite the fact that a product may have been evaluated several times. So I want the product once, and if evaluated, then I want the result from the last evaluation only. I have succeeded in doing this, BUT there is allways somethings one forget in the joy of success! A product may be evaluated several times by several different companies, and the user that logs on should only see the evaluation done by his/her company. Well, suddenly my query doesn't work like it should anymore. In the query I have attached as a link (which uses a view, also attached as a link), you will see that the code says "HAVING r.levid = 1000002". The number is the unique ID for a company that is loged on. If you switch this to anything else, say 1000003 and rerun the query, you will see that it returns two rows less. The products "Anolit A, Anolit Extra og Anolit Extra A" and "DynoRex" are not returned if they have been evaluated by another company, but not the company that is loged on. (for example "HAVING r.levid = 1000003" will return two rows less). I am completely stuck. Done my best, and out of new ideas. I would appretiate any help. Don't hesitate to ask if I have missed out some information. Thanx, H :-) *** Sent via Developersdex http://www.developersdex.com *** Try the following,
In your query where you have: FROM hms1 h1 LEFT OUTER JOIN riskAssessementGroup rag INNER JOIN riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID ON ra.hms_id = h1.hms_id change it to: FROM hms1 h1 LEFT OUTER JOIN riskAssessementGroup rag INNER JOIN riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID ON ra.hms_id = h1.hms_id And rag.levid = 1000002 Then, where you have: (SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid HAVING r.levid = 1000002) change it to: (SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid) So that your query looks like: SELECT h1.trade_name, h1.hms_id, h1.manufacturer, h1.msdsid, rag.riskAssessementGroupID, rag.createdDate, ra.substitutStatus FROM hms1 h1 LEFT OUTER JOIN riskAssessementGroup rag INNER JOIN riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID ON ra.hms_id = h1.hms_id And rag.levid = 1000002 WHERE (rag.riskAssessementGroupID IN (SELECT MAX(rag.riskAssessementGroupID) AS riskAssessementGroupID FROM riskAssessementGroup rag INNER JOIN riskAssessement ra ON ra.riskAssessementGroupID = rag.riskAssessementGroupID RIGHT OUTER JOIN hms1 hh ON ra.msdsid = hh.msdsid GROUP BY hh.trade_name, hh.manufacturer, rag.levid HAVING EXISTS (SELECT DISTINCT (MAX(r.createdDate)) FROM v_getRiskAssessementGID r GROUP BY r.msdsid, r.levid) AND hh.manufacturer LIKE '%dyno%') OR rag.riskAssessementGroupID IS NULL) AND (h1.manufacturer LIKE '%dyno%') order by trade_name HTH, Tom Show quote "kongsballa" <kongsgba***@devdex.com> wrote in message news:uayiMt4xGHA.4660@TK2MSFTNGP02.phx.gbl... > Hey! > > I am having some trouble with a query. I will try to explain the > scenario and how the tables relate and what they are used for. The > table- and viewdefinitions and inserts(I have only included the columns > needed for the query) are located here: > http://norskwebforum.no/pastebin/5988 > > The query I have made and that almost works is located here: > http://norskwebforum.no/pastebin/5989 > > By the way, this is SQL Server 2000. > > Well, down to action. I have a table that consists of chemical products. > A product can be compared with other products regarding health, fire and > environment risk. The products are evaluated based on criterias and the > whole riskevaluation is loged for further use. One product may be > evaluated several times by several organisations. > > If a user searches for a product that has been evaluated then he or she > should see the status from the last evaluation even if several have been > conducted. > > The way I save the evaluation data is one table that holds a "groupID", > dateCReated, conclusion, etc... Then in a one-too-many relation I then > have a table with a FK too the "groupID" that holds information about > which products were evaluated and what score they got. > > Now what I need, and causes me trouble is the following: > When searching for (or showing a product), I want a distinct resultset > where a product only occurs once, despite the fact that a product may > have been evaluated several times. So I want the product once, and if > evaluated, then I want the result from the last evaluation only. I have > succeeded in doing this, BUT there is allways somethings one forget in > the joy of success! A product may be evaluated several times by several > different companies, and the user that logs on should only see the > evaluation done by his/her company. Well, suddenly my query doesn't work > like it should anymore. > > In the query I have attached as a link (which uses a view, also attached > as a link), you will see that the code says "HAVING r.levid = 1000002". > The number is the unique ID for a company that is loged on. If you > switch this to anything else, say 1000003 and rerun the query, you will > see that it returns two rows less. The products "Anolit A, Anolit Extra > og Anolit Extra A" and "DynoRex" are not returned if they have been > evaluated by another company, but not the company that is loged on. (for > example "HAVING r.levid = 1000003" will return two rows less). > > I am completely stuck. Done my best, and out of new ideas. > > I would appretiate any help. Don't hesitate to ask if I have missed out > some information. > > Thanx, H :-) > > *** Sent via Developersdex http://www.developersdex.com *** Tom, your an angel!
It worked like a charm. Now I will be able to continue on my project. Thanx, H :-) *** Sent via Developersdex http://www.developersdex.com ***  |
|||||||||||||||||||||||
Other interesting topics