Help in Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in Subquery

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-03 : 10:54:02

Need help writing a subquery

Iam trying to write a query on a table called policies which has the below columns
1)reqid
2)policy
3)salesoffice
4)businessid

Each policy table can save multiple reqid's or just 1 reqid's

I want to write a query such that I need all data(*) associated with list of all the reqid's from the policy table where count(reqid) >1

Iam stuck with the below query

select * from policy where reqid in (select reqid,count(reqid)
from policy
group by requestid
having
count(requestid) >1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-03 : 10:58:39

I don't think you need the subquery

select count(distinct reqid),<stuff>
from policy
group by <stuff>
having count(distinct reqid) > 1

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 10:59:22

select * from policy where reqid in (select reqid from policy
group by requestid
having
count(requestid)) >1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 11:00:24

quote:
Originally posted by jimf

I don't think you need the subquery

select count(distinct reqid),<stuff>
from policy
group by <stuff>
having count(distinct reqid) > 1

Jim

Everyday I learn something that somebody else already knew

It is needed because OP wants to list out all columns. You can use join also instead of subquery

Madhivanan

Failing to plan is Planning to fail

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-03 : 11:03:39

I tried to execute the query and did not know where the syntax error really is

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 11:16:23

Execute the code I posted

Madhivanan

Failing to plan is Planning to fail

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-03 : 11:24:46