Solved

How to generalize this query for all instances

Posted on 2011-02-20
8
470 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 40

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
 
LVL 40

Expert Comment

by:Sharath
ID: 34939384
Can you post the query which yields the result set in http:#34938591
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 40

Accepted Solution

by:
Sharath earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now