Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to generalize this query for all instances

Posted on 2011-02-20
8
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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