0

Following SQL query:

select *  from er_101
 where cd_relnaam IN ( 
     select cd_relnaam 
     from er_101 
     group by cd_relnaam 
     having count(*) > 1)
 AND ld_relopdrachtgever = '1'

Though I need to have that sub query also limits on ld_relopdrachtgever = '1'
How is that possible with an HAVING statement?

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Patrick Rennings
  • 191
  • 1
  • 5
  • 19
  • Please give us the structure of your Table and what you want to achieve because it's not very clear right now. – xray1986 Oct 26 '12 at 10:53
  • The subquery isnt specified with an value like the main query: AND ld_relopdrachtgever = '1' I also needed that In my subquery otherwise I get results in my resultset that have multipli values but not with ld_relopdrachtgever equel to 1 – Patrick Rennings Oct 26 '12 at 10:54
  • 1
    The subquery limit you can do by adding `WHERE Id_relopdrachtgever='1'` before grouping by `cd_relnaam`. – LSerni Oct 26 '12 at 10:56

1 Answers1

2

You can also use WHERE in sub-query.

SELECT * FROM er_101
 WHERE cd_relnaam IN ( 
     SELECT cd_relnaam 
     FROM er_101 
     WHERE ld_relopdrachtgever = '1'  <--You can add WHERE clause before GROUP BY
   --^^^^^----
     GROUP BY cd_relnaam 
     HAVING COUNT(*) > 1)
 AND ld_relopdrachtgever = '1'
Himanshu
  • 31,810
  • 31
  • 111
  • 133