Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to generalize this query for all instances

Posted on 2011-02-20
8
Medium Priority
?
490 Views
Last Modified: 2012-05-11
The data has multiple subsites, plots, and sections. I need to run a query that does for each combination of subsite,plot,section what the query below does for one combination of subsite_id,plot,section. I assume that there is a loop to set up but am not sure what the syntax should be.

delete from SAMPLING_RESULTS
where OBSERVATION_ID in (
      select  sr.OBSERVATION_ID
      from sampling_entity se join SAMPLING_RESULTS sr on se.OBSERVATION_LOCATION_ID = sr.OBSERVATION_LOCATION_ID join monitoring_location ml on ml.monitoring_location_ID = se.MONITORING_LOCATION_ID
      where exists (
      select  sr.OBSERVATION_ID, sr.OBSERVATION_LOCATION_ID,sr.SPECIES_CODE
      from sampling_entity se join SAMPLING_RESULTS sr on se.OBSERVATION_LOCATION_ID = sr.OBSERVATION_LOCATION_ID
    where se.SAMPLING_ENTITY_NAME = 1 )
      AND exists (
      select  sr.OBSERVATION_ID, sr.OBSERVATION_LOCATION_ID,sr.SPECIES_CODE
      from sampling_entity se join SAMPLING_RESULTS sr on se.OBSERVATION_LOCATION_ID = sr.OBSERVATION_LOCATION_ID
      where se.SAMPLING_ENTITY_NAME in (2,3,4,9)
      )
      and not se.SAMPLING_ENTITY_NAME in (1,5,6,7,8) and subsite_id = 10 and plot = 2 and section = 'a'
      )
0
Comment
Question by:dblankman
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34938409
By looking at your query, I do not understand what you are trying to achieve? You are considering SAMPLING_ENTITY_NAME = 1 records in one WHERE clause, SAMPLING_ENTITY_NAME in (2,3,4,9) records in another WHERE clause but excluding SAMPLING_ENTITY_NAME in (1,5,6,7,8) records in final WHERE clause.
Instead ofjoining the three table multiple times, I think you can avoid those multiple JOINs. Post some sample data with expected result.
0
 

Author Comment

by:dblankman
ID: 34938591
There has been duplicate data that was entered. It is not simple duplication, that is, not identical rows, but duplication that results from species entered in entities 2,3,4, or 9 that was also entered in 1.  For example a select query yields the data shown below. Then ending dataset should have a species entered only once for a given subsite_id,plot,section. For example the species bisdid appears twice. Once in entity 4 and again in entity 9. In this case it needs to be deleted from entity 9. Other duplications are in bold. This data is for one subsite,one plot, one section. The duplications need to be deleted for each combination of subsite,plot,section. In each case the row with the lowest entity name is the one that needs to remain.

On further examination the delete query that I wrote initially was incorrect.

subsite_id  plot        section OBSERVATION_ID entity_name species
----------- ----------- ------- -------------- ----------- -------
10          2           d       3105           9           allsta
10          2           d       3093           9           anaarv
10          2           d       3043           4           anecor
10          2           d       3044           4           astlin
10          2           d       3034           1           aveste
10          2           d       3048           4           bisdid
10          2           d       3088           9           bisdid

10          2           d       3035           1           bradis
10          2           d       3101           9           catlut
10          2           d       3089           9           cencya
10          2           d       3090           9           consic
10          2           d       3050           4           consic

10          2           d       3091           9           crumac
10          2           d       3087           9           cycper
10          2           d       3036           3           cycper

10          2           d       3102           9           cyncre
10          2           d       3038           3           euparg
10          2           d       3084           9           euparg

10          2           d       3040           4           fercom
10          2           d       3103           9           gerhyb
10          2           d       3049           4           gerrot
10          2           d       3107           9           hedrha
10          2           d       3106           9           horbul
10          2           d       3052           4           lotper
10          2           d       3086           9           medcor
10          2           d       3039           3           morsis
10          2           d       3082           9           onosqu
10          2           d       3079           9           ornnar
10          2           d       3095           9           palspi
10          2           d       3078           9           pharup
10          2           d       3098           9           pipbla
10          2           d       3083           9           ranasi
10          2           d       3108           9           rhaste
10          2           d       3097           9           rubten
10          2           d       3042           4           sarspi
10          2           d       3099           9           scaibe
10          2           d       3047           4           scomur
10          2           d       3096           9           scopap
10          2           d       3104           9           shearv
10          2           d       3092           9           shearv
10          2           d       3081           9           sinalb
10          2           d       3041           4           sonole
10          2           d       3094           9           sonole

10          2           d       3046           4           thecyn
10          2           d       3080           9           thrtub
10          2           d       3085           9           tricam
10          2           d       3109           9           triste
10          2           d       3037           3           uropic
10          2           d       3045           4           valhis
10          2           d       3051           4           vicpal
10          2           d       3100           9           vicpal


(51 row(s) affected)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34938850
do you mean this?

delete where the species is recorded more than once for a subsite,plot,section combination
and the row is not the latest row as defined by entity_name ascending

?
Delete from Yourtable
Where Exists (select subsite_id 
                from (
                select subsite_id,plot,section,species,max(entity_name) as max_entity
                  from yourtable as x
                 group by subsite_id,plot,section,species
                Having count(*) > 1
                     ) AS X
                wHERE YOUrTABLE.SUBSITE_ID=x.SUBSITE_ID
                  AND yourtable.plot=x.plot
                  and yourtable.section=x.section
                  and yourtable.species=x.species
                  and yourtable.entity_name<x.max_entity
             )

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 41

Expert Comment

by:Sharath
ID: 34939384
Can you post the query which yields the result set in http:#34938591 
0
 

Author Comment

by:dblankman
ID: 34942854
Here is the query that produced the dataset as represented in http:#34938591


select ml.subsite_id,ml.plot,
      cast(ml.section as CHAR(1)) as section,
      sr.OBSERVATION_ID,
      cast(se.SAMPLING_ENTITY_NAME as CHAR(1)) as entity_name,
      cast(sr.SPECIES_CODE as CHAR(6)) as species

from sampling_entity se join SAMPLING_RESULTS sr on                                            se.OBSERVATION_LOCATION_ID = sr.OBSERVATION_LOCATION_ID join             monitoring_location ml on ml.monitoring_location_ID =       se.MONITORING_LOCATION_ID
      
where not se.SAMPLING_ENTITY_NAME in (5,6,7,8)
      and subsite_id = 10 and plot = 2 and section = 'd'

order by SPECIES_CODE,subsite_id,plot,section
0
 

Author Comment

by:dblankman
ID: 34944119
the solution from lowfatspread assumes that all of the fields are from the same table. The design of this database is a master/detail/detail.  the reality that is represented by this database is a physical area such that there is a "monitoring location" which represents a section (a,b,c,d) within a plot and plots are contained within subsites. This is one table. Then there is a sampling entity table. This table has values of 1,2,3,4,5,6,7,8,9. The third table is a results table in which individual plant species are recorded within a given sampling entity. In an ideal situation if a plant was identified in entity 1, it would not be recorded in 2,3,4, or 9 or if identified in 5 would not be recored if found in 5,6,7,8 or 9. However, triggers were not setup to prevent this so there are species recorded more than once.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34944167
Can you try this?
;WITH cte1 
     AS (SELECT ml.subsite_id, 
                ml.plot, 
                CAST(ml.section AS CHAR(1))              AS section, 
                sr.OBSERVATION_ID, 
                CAST(se.SAMPLING_ENTITY_NAME AS CHAR(1)) AS entity_name, 
                CAST(sr.SPECIES_CODE AS CHAR(6))         AS species 
           FROM sampling_entity se 
                JOIN SAMPLING_RESULTS sr 
                  ON se.OBSERVATION_LOCATION_ID = sr.OBSERVATION_LOCATION_ID 
                JOIN monitoring_location ml 
                  ON ml.monitoring_location_ID = se.MONITORING_LOCATION_ID 
          WHERE NOT se.SAMPLING_ENTITY_NAME IN (5,6,7,8) 
                AND subsite_id = 10 
                AND plot = 2 
                AND section = 'd'), 
     cte2 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY SPECIES_CODE,subsite_id,plot,section ORDER BY entity_name) rn
           FROM cte1) 
  SELECT * 
    FROM cte2 
   WHERE rn = 1 
ORDER BY SPECIES_CODE, 
         subsite_id, 
         plot, 
         section

Open in new window

0
 

Author Closing Comment

by:dblankman
ID: 34951639
Thanks. I have not worked with common table expressions before.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question