Combine sql queries

snufse · June 16th, 2008, 02:48 PM

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

Old Pedant · June 16th, 2008, 03:46 PM

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?

snufse · June 16th, 2008, 06:23 PM

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.

Old Pedant · June 16th, 2008, 08:08 PM

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???

snufse · June 17th, 2008, 08:22 AM

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.

snufse · June 17th, 2008, 10:28 AM

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?

Old Pedant · June 17th, 2008, 12:27 PM

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.

snufse · June 17th, 2008, 12:37 PM

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....

Old Pedant · June 17th, 2008, 01:08 PM

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.

snufse · June 17th, 2008, 01:17 PM

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 .....