SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How To group multiple queries in single sql stmt?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  13:39:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 02/14/2013 :  14:01:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 02/14/2013 :  14:04:47  Show Profile  Visit webfred's Homepage  Reply with Quote
UNION or UNION ALL?


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

jchoudja
Starting Member

USA
41 Posts

Posted - 02/14/2013 :  14:45:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 02/14/2013 :  15:36:53  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/14/2013 :  23:10:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000