sql - a query to find duplicates but exclude set -

i have table following columns

emp_id work_date element 

i want query (modified version of below query) return single row if there more 1 row given work_date , employee id (group emp_id , work_date) in above table. have written query like:

select emp_id, work_date  mytable work_date = :p_workdate group emp_id, work_date having count (1) > 1 

for example:

emp_id  work_date 1       1/1/2013 1       1/1/2013 2       1/1/2013 

query should return follows if pass 1/1/2013 :p_workdate:

1    1/1/2013 

basically trying find if there more 1 row emp_id , work_date there additional requirement element column contains - if contains values set (element1 + element2) or (element3 + element4).

the additional requirements (following) depend upon element column contains (if rows have values set).

1) there 2 rows same emp_id , work_date , 1 of element column element1 , other element column element2 (a set of element1 , element 2)

for example:

emp_id  work_date element 1   1/1/2013  element1 1   1/1/2013  element2 

the query should not return row because if there 2 rows, set (element1 , elelement2)

2) there 2 rows same emp_id , work_date , 1 of element column element3 , other element column element4 (a set of element3 , element 4)

for example:

emp_id  work_date element 1   1/1/2013  element3 1   1/1/2013  element4       

the query should not return row because if there 2 rows, set (element3 , element 4)

3) if there 2 rows same emp_id , work_date , not set above should return row

for example:

emp_id  work_date element 1   1/1/2013  element1 1   1/1/2013  xxx    

(same result if element column had element2 instead of element1)

query should return follows if pass 1/1/2013 :p_workdate:

1    1/1/2013 

4) if there more 2 rows same emp_id , work_date, no matter element column contains, should return row.

for example:

emp_id  work_date element 1   1/1/2013  element1 1   1/1/2013  element2 1   1/1/2013 xxx 

query should return follows if pass 1/1/2013 :p_workdate:

1 1/1/2013

---

thank you

give go ... think got logic straight:

select   emp_id,   work_date (   select     emp_id,     work_date,     min(element) min_element,     max(element) max_element,     count(*) rows_counted       mytable       work_date = :p_workdate   group     emp_id,     work_date   having     count (*) > 1)   rows_counted > 2 or   (min_element,max_element) not in (select 'element1' el1, 'element2' el2 dual union                                     select 'element3' el1, 'element4' el2 dual)