CODEWITHSUNDEEP
sqlsql-server
S Walden
S Walden

Reputation: 23

Select records where there are more than one value in one of the fields

Could someone please help me with narrowing down my results? Three tables facility, sample, location. With the code below, I get expected results but I would like to show only the records where the matrix code has more that one result (>1). In the example below, I would like to show only location code 0689. 

SELECT DISTINCT f.facility_code, 
                l.sys_loc_code, 
                l.loc_type, 
                s.matrix_code
FROM dt_sample s join dt_location l on l.facility_id = s.facility_id AND l.sys_loc_code = s.sys_loc_code 
JOIN dt_facility f on f.facility_id = l.facility_id
WHERE l.loc_type ='TS'
GROUP BY f.facility_code, l.sys_loc_code, l.loc_type, s.matrix_code
HAVING COUNT(s.matrix_code)>1
ORDER BY f.facility_code, l.sys_loc_code, s.matrix_code

facility_code   sys_loc_code    loc_type    matrix_code  
DUR03           0687             TS         WATER        
DUR03           0688             TS         WATER        
DUR03           0689             TS         SEDIMENT     
DUR03           0689             TS         SW           
DUR03           0689             TS         WATER

Thanks for your help.

Upvotes: 2

Views: 45

Answers (1)

SqlZim
SqlZim

Reputation: 38073

One option using exists() to check if a different matrix_code exists for a given facility_id and sys_loc_code:

select distinct 
    f.facility_code
  , l.sys_loc_code
  , l.loc_type
  , s.matrix_code
from dt_sample s
  inner join dt_location l 
    on l.facility_id = s.facility_id 
   and l.sys_loc_code = s.sys_loc_code
  inner join dt_facility f
   on f.facility_id = l.facility_id
where l.loc_type = 'TS'
  and exists (
    select 1
    from dt_sample i
    where i.facility_id  =  s.facility_id
      and i.sys_loc_code =  s.sys_loc_code
      and i.matrix_code  <> s.matrix_code
  )
order by 
    f.facility_code
  , l.sys_loc_code
  , s.matrix_code

Upvotes: 1

Related Questions