Sub query - 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

Sub query

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-06-30 : 12:15:46

I have a query below that returns exactly what I need it to, however I would like to add more fields to help the user make decisions abotu the data.

Query (returns 55 rows):
select na.ID,preferred_bill,count(na.preferred_bill) as CountOfPreferredBill
from Name_address na
group by na.preferred_bill,na.id
having count(na.preferred_bill) > 1
and preferred_bill = 1

Results:
ID Preferred_Bill CountOfPreferredBill
101 1 2
102 1 3
103 1 2

I would like to pull a couple more fields from na. My attempt below returns 17478 rows. I need only the original 55 rows and their additional fields.

SELECT ID, purpose
FROM name_address
WHERE exists (select na.ID,preferred_bill,count(na.preferred_bill) as CountOfPreferredBill
from Name_address na
group by na.preferred_bill,na.id
having count(na.preferred_bill) > 1
and preferred_bill = 1)

Thanks,
dz

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 12:27:38

[code]
SELECT *
FROM Name_address nao
WHERE EXISTS (
SELECT nai.ID,nai.preferred_bill
FROM Name_address nai
WHERE preferred_bill = 1
AND nai.preferred_bill = nao.preferred_bill
AND nai.id = nao.id
GROUP BY nai.preferred_bill,nai.id
HAVING COUNT(nai.preferred_bill) > 1)
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dzabor
Posting Yak Master

138 Posts

Posted - 2010-06-30 : 12:37:01

It worked!

I always assumned the select statment had to be able to stand on its own meaning the nao part would break it since it is not part of the select. so I should link the sub query with the main query (Which makes sense when creating reports in Crystal and access) within the sub report.

Thanks for the response and would approciate any addtional thoughts - this is awesome!

dz

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 12:46:07

Not a bad turn around time for 10 minutes on a free board, eh

Other thoughts? Yeah, Lose SELECT * and only list the columns you want (since I didn't know what they were)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources