?
Solved

Help with SQL statement

Posted on 2008-11-04
8
Medium Priority
?
212 Views
Last Modified: 2010-04-21
I have a couple of different sql queries that each in their own right give me what I need. But I would like to merge the two together as one report.

Is it possible to have an outer select with an inner select?  

The first select is returning an ID. On the second (inner) select I would like to pass in the ID from the outer select.

How could I do this?

Thanks
0
Comment
Question by:greddin
  • 4
  • 3
8 Comments
 
LVL 3

Expert Comment

by:dhanushkad
ID: 22878350
Code you please explain it more..
0
 
LVL 3

Expert Comment

by:dhanushkad
ID: 22878359
Could you please explain your question with some samples..
0
 

Author Comment

by:greddin
ID: 22878690


First first select statement returns a list of CollectionIDs Like this:
 
CollectionID
------
1
2
3
4
5
and so on
 
My second query does takes a given CollectionID and counts a DocType that are in each CollectionID.
 
Like this:
 
DocType   Total
-------   -----
 type1      12
 type2       6
 type3       8
 type4       2
 type5      16
 
I would somehow like to merge these two results into one and return something like this:
 
CollectionID  DocType  Count
------------  -------  -----
     1         type1     12
     1         type2      6
     1         type3      8
     1         type4      2
     1         type5     16
     2         type1      2
     2         type2     10
     2         type3      5
     2         type4      3
     2         type5      9
..continue until all CollectionIDs are done.
 
Does this make sense?

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22878717
select collectionid, doctype,count(*) as 'Count'
from YourDocumentTable
group by collectionid, doctype
0
 
LVL 3

Expert Comment

by:dhanushkad
ID: 22879845
IS the above comment is what your looking for ?
or
do you have two seperate queries for frist and secrion statements. >
0
 

Author Comment

by:greddin
ID: 22880183
Right now I have two separate queries that run on different tables of the same database.
0
 
LVL 3

Expert Comment

by:dhanushkad
ID: 22880435
what r the queries ?
0
 

Author Closing Comment

by:greddin
ID: 31513174
Thanks for this response. Based on this I was able to get what I needed.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question