Question on JOIN

  • I am going to try and make the example easier to follow, in hopes I can explain myself properly.

    I have two tables named: LiveRun and Run

    The LiveRun table has three columns: nRunId, nTotal, nWaste

    The Run table has 4 columns: nRunId, nTotal, nWaste, nProductRunId

    The Run table stores a history of all the runs that have run in the past. If you were to group the Run table by nProductRunId, you can get the nTotal and nWaste for the "Product Run". The problem is, that it may leave out an active run in the LiveRun table that needs to be included in that nTotal and nWaste.

    So the question is, how could I write the join that will sum these two tables up to give a LIVE look at the current total (past runs and present). If I join the two tables, this is how I would do it:

    FROM LiveRun (NOLOCK) LEFT OUTER JOIN Run ON LiveRun.nRunId = Run.nRunId

    but no matter how I join this (left outer join or right outer join) I get either: 1 record (from the LiveRun table) or all the records in the Run table as NULLS but one record that ties to the LiveRun. The problem is there may be three other records in the Run table that I want to sum up nTotal and nWaste.

    Hope this makes sense....any suggestions?

    Cheers!

  • nailers (4/11/2011)


    I am going to try and make the example easier to follow, in hopes I can explain myself properly.

    I have two tables named: LiveRun and Run

    The LiveRun table has three columns: nRunId, nTotal, nWaste

    The Run table has 4 columns: nRunId, nTotal, nWaste, nProductRunId

    The Run table stores a history of all the runs that have run in the past. If you were to group the Run table by nProductRunId, you can get the nTotal and nWaste for the "Product Run". The problem is, that it may leave out an active run in the LiveRun table that needs to be included in that nTotal and nWaste.

    ...

    Is nRunID the pk of both tables?

    How do you identify - in words - a row in LiveRun which you want to be included? I reckon it should be like this: "Get rows from LiveRun where the pk (nRunID) isn't in Run"

    It would help if you could post the CREATE TABLE statements (DDL) for both tables.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • It is far from clear what you want.

    I suspect you may need to use UNION ALL instead of JOIN.

    Maybe something like:

    ;WITH Combined

    AS

    (

    SELECT nRunId, nTotal, nWaste, -1 AS nProductRunId

    FROM LiveRun

    UNION ALL

    SELECT nRunId, nTotal, nWaste, nProductRunId

    FROM Run

    )

    SELECT nProductRunId, SUM(nTotal) AS nTotal, SUM(nWaste) AS nWaste

    FROM Combined

    GROUP BY nProductRunId

  • Since you don't have a productID for the live runs, which groupings do you want them totalised into??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply