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
 How To group multiple queries in single sql stmt?

Author  Topic 

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 13:39:36
Hi, I am currently working to create RDLC reports using and existing SQL DB. I am allow to use a single SQL statement to create Dataset in each of my report. Problem is that some report contain more than 20 tables and it become hard to manage.
I use to create mutiple seperate sql queries and use them to build my final query. My question is how can I do this in a single SQL statment.
Exemple droup tables A, B, and C As T1, Tables D, E, F, G As T2 ...
and use T1 and T2 to populate my table.

The reason of this is, Customers alrady have their my Database and nothing should be changed on the structure each time a new report is added. So the Report should contain the sql statement to collect data from existing table.

Thank you.

jc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 14:01:55
You could use common table expressions or subqueries. Can you post a sample of the multiple queries that you currently have that you are trying to consolidate into a single query?
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-14 : 14:04:47
UNION or UNION ALL?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

jchoudja
Starting Member

41 Posts

Posted - 2013-02-14 : 14:45:13
Thank you but I think Union will help me add same raws.
I want to use relation between my queries to build my table. So add columns to my table.
Let say, I Have 2 queries
SELECT ID, Name, FistName, DOB
FROM person

and I have
SELECT personID, InvoiceNo, InvoiceDate
FROM invoice

How can I combine those 2 in a single query. Unsing ID and personID for relation between the 2 queries
Note. I don't want a single select. This exemple is simple but what I want to do is more complex
result should show like
ID | Name | FistName | DOB |InvoiceNo | InvoiceDate

Thank you

jc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 15:36:53
quote:
How can I combine those 2 in a single query. Unsing ID and personID for relation between the 2 queries
Note. I don't want a single select. This exemple is simple but what I want to do is more complex
result should show like
ID | Name | FistName | DOB |InvoiceNo | InvoiceDate
I thought you wanted a SINGLE select, so not sure if the query below will meet your needs.
SELECT
p.ID,
p.Name,
p.FistName,
p.DOB,
i.InvoiceNo,
i.InvoiceDate
FROM
Person p
LEFT JOIN Invoice i ON i.personID=p.ID;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 23:10:02
I don't want a single select

Why you don't want it in single select? Are you looking at initially populating table with one set of values and then updating it with related values later?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -