Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 16th, 2008, 02:48 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default Combine sql queries

need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query

Query 1


Code:
SELECT         dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
                      dbo.Job.ChangeDate
FROM            dbo.Job 
                      LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                      LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE        dbo.job.CompanyJobId = 3505048
ORDER BY   dbo.Job.CompanyJobId
Query 2


Code:
SELECT         case dbo.SourceType.CompanySourceTypeId
                    when 'PR' then SUM(dbo.ProductionEvent.Quantity)
               end
               AS Ttl_Qty
FROM           dbo.Batch INNER JOIN
               dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
               dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
               dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
               dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
               dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
               dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
               dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
                 dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid 
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY       dbo.SourceType.CompanySourceTypeId
I have tried this but it does not work:


Code:
SELECT          dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, 
                dbo.Job.ChangeDate, Ttl_Qty

FROM            dbo.Job 
                      LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
                      LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
                      LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE        dbo.job.CompanyJobId = 3505048 and Ttl_Qty in(

SELECT         case dbo.SourceType.CompanySourceTypeId
                    when 'PR' then SUM(dbo.ProductionEvent.Quantity)
               end
               AS Ttl_Qty
FROM           dbo.Batch INNER JOIN
               dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
               dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
               dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
               dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
               dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
               dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
               dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
                 dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid 
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY       dbo.SourceType.CompanySourceTypeId)


ORDER BY   dbo.Job.CompanyJobId
Reply With Quote
  #2 (permalink)  
Old June 16th, 2008, 03:46 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Can I first ask a question?

Why do you do
Code:
SELECT         case dbo.SourceType.CompanySourceTypeId
                    when 'PR' then SUM(dbo.ProductionEvent.Quantity)
               end
when your WHERE clause is already doing
Code:
WHERE ... dbo.SourceType.CompanySourceTypeId = 'PR'
???

Given the WHERE clause, there will never be any records where that CASE WHEN will actually be doing anything. Which I think is a good thing, since you don't provide any ELSE in the CASE WHEN.

So did you find that, for some reason, the CASE WHEN is required?
Reply With Quote
  #3 (permalink)  
Old June 16th, 2008, 06:23 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

If I leave out "dbo.SourceType.CompanySourceTypeId = 'PR'" then I get additional 2 records with null values. There are some 'MA' records. I might be able to leave out the "CASE WHEN" since I am always selecting the 'PR' records only. Good catch. Now, are you able to help me with the "sub select" ? I will be need additional 3 sub select before I'm finished but wanted to start with this one first. I guess you could use "join" but I fell better using "sub select" if possible. Thank you.

Reply With Quote
  #4 (permalink)  
Old June 16th, 2008, 08:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, I think the most effective way to do this is to create and use a stored procedure.

Like this:
Code:
CREATE PROCEDURE anyNameYouWant
AS
DECLARE @total INT

SET NOCOUNT ON

SELECT @total = SUM(dbo.ProductionEvent.Quantity)
FROM dbo.Batch 
     INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid 
     INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid 
     INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid 
     INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
     INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid 
     INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid 
     LEFT OUTER JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid 
     LEFT OUTER JOIN dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid 
WHERE dbo.Job.CompanyJobId = 3505048 
  AND ISNULL(dbo.SourceType.CompanySourceTypeId,'') = 'PR'

SET NOCOUNT OFF

SELECT J.CompanyJobId, J.Name, dbo.Region.CompanyRegionID, J.Active, J.ChangeDate, @total AS Ttl_Qty
FROM dbo.Job AS J
     LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
     LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
     LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE     dbo.job.CompanyJobId = 3505048
ORDER BY  dbo.Job.CompanyJobId
But I can't see any reason for including those two LEFT JOIN tables in that first query. Their presence can't possibly affect the value you get for the SUM( ), since they don't participate in the WHERE clause. That is, no matter whether there is a matching record in dbo.Product and/or dbo.JobNoteEvent, the dbo.ProductionEvent.Quantity values found (and then SUMmed) will be the same.

So at least we should be able to omit them.

Similarly, in the second query, I can't see any point in the LEFT JOIN to dbo.JobType, since nothing from that table appears in either the SELECT or in the WHERE clause.

And as long as we are doing all that, let's allow you to pass in both the JobId and CompanySourceTypeId that you want to do all this for.

So...

I'm pretty sure you get the same results if you just do this:
Code:
CREATE PROCEDURE anyNameYouWant
    @jobid INT,
    @typeid VARCHAR 20
AS
DECLARE @total INT

SET NOCOUNT ON

SELECT @total = SUM(dbo.ProductionEvent.Quantity)
FROM dbo.Batch 
     INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid 
     INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid 
     INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid 
     INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid 
     INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid 
     INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid 
WHERE dbo.Job.CompanyJobId = @jobid
  AND ISNULL(dbo.SourceType.CompanySourceTypeId,'') = @typeid

SET NOCOUNT OFF

SELECT J.CompanyJobId, J.Name, dbo.Region.CompanyRegionID, J.Active, J.ChangeDate, @total AS Ttl_Qty
FROM dbo.Job AS J
     LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid 
     LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid 
WHERE     dbo.job.CompanyJobId = @jobid
ORDER BY  dbo.Job.CompanyJobId
Finally, why do you need an ORDER BY CompanyJobId when you will only show ONE jobid???
Reply With Quote
  #5 (permalink)  
Old June 17th, 2008, 08:22 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Hi Old Pedant, Want to thank you for spending time to really help me out. The one query may be reduced to somethin lige this, rendering the same result:

Code:
SELECT                 SUM(dbo.ProductionEvent.Quantity)
FROM                   dbo.Batch INNER JOIN
                       dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                       dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                       dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid  INNER JOIN
                       dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                       dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid 
WHERE                  dbo.job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY               dbo.SourceType.CompanySourceTypeId
I will test it out and get back to you with the results.

PS The reason I am using one JobNumber is for testing only, there will be 3 parameters passed in:

1. A specific Job Number
or
2. Date Range from/to

Again, I very much appreciate all your help.

Reply With Quote
  #6 (permalink)  
Old June 17th, 2008, 10:28 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Did some testing, created the sp and a web page loading the data into a gridview. The Total Quantity (Ttl_Qty) was retrived and displayed. Great!!!
Next step is to be able to select a job number (dbo.Job.CompanyJobId ) by a specific given job number or select a date range for displaying a list of jobs (using dbo.Job.ChangeDate >= @DateFrom and dbo.Job.ChangeDate <= @DateTo). I have added these parms to my sp like this:

Code:
CREATE PROCEDURE sp_Vecellio_Test1
   @JobNumber double,
   @DateFrom datetime,
   @DateTo datetime,
   @TypeId varchar(20)


In this case I need to loop thru the job table first and then get the total quantity for each job being selected. How will that impact the code in my sp?



Reply With Quote
  #7 (permalink)  
Old June 17th, 2008, 12:27 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Oh, please! No loops! The whole point of using tables is to avoid loops.

So you are saying you might call this with *EITHER* a single job number *OR* a date range? Would you ever call it with both?? And I assume that you call with @TypeID in all cases??

I think you are going to end up doing and IF test in your SP and going one rout or the other, but more details, please.
Reply With Quote
  #8 (permalink)  
Old June 17th, 2008, 12:37 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Sorry about that, either you will call with a single job number request or a date range (from/to date), not both. If you call with date range, selected jobs will be listed in a data view. As for the typeid, it will always be 'MA' (for received material) and 'PR' (for production material used). The 'MA' and 'PR' are the same select except for type (I can handle that myself). Again sorry if I did not explain everything, I just wanted to go one step at a time....

Reply With Quote
  #9 (permalink)  
Old June 17th, 2008, 01:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

But if the two USAGES of the SP will be different (that is, one will produce a grid and the other won't), then why try to stuff both usages in to a single SP in the first place??

It's not illegal or immoral. It's just more complex.

If the two usages are separate SP's, then you don't have to have any "IF" logic, just for starters.
Reply With Quote
  #10 (permalink)  
Old June 17th, 2008, 01:17 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Both the single and the multiple option will fill the same grid (either a single line or many lines). The sp behind the scene can be different sp's, I which one to call based upon the user selection. The reason we are always filling the grid is that there are "select" options to do further drill downs into details. Nort sure if I made myself clear .....

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine two queries snufse SQL Server 2005 6 June 24th, 2008 09:42 PM
Help: Need to combine multiple IF queries scotts SQL Server 2005 1 April 14th, 2008 07:54 PM
Anyone Expert in SQL Queries? itHighway HTML Code Clinic 3 June 3rd, 2005 09:57 AM
combine 2 queries collie SQL Server 2000 2 November 29th, 2004 03:09 PM
Combine queries lryckman Access VBA 2 May 11th, 2004 11:38 AM



All times are GMT -4. The time now is 09:15 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.