Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Help with a subquery join

    I have a query that produces the following results:

    Code:
    wbs2   wbs3   Name                   AmtBud     BTD             BillExt
    100      100    Traffic Impact        10000      13563.75        0
    140             Highway/Signal         0         0               0
    140      100    Highway/Signal         0         0               0
    and another query that produces the follwing results:

    Code:
    wbs2    FeeType
    100       SC
    140       HM&SC
    150       HM
    160       HM&SC
    180       SC
    190       SC
    So, I want to join both queries to produce the ending result:
    Code:
    wbs2   wbs3   Name                   AmtBud     BTD             BillExt  FeeType 
    100      100    Traffic Impact        10000      13563.75        0        SC
    140             Highway/Signal         0         0               0        HM&SC
    140      100    Highway/Signal         0         0               0        HM&SC
    Here are my queries:
    first query:
    Code:
    SELECT     CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2, 
                          WM_Template.WBS3 AS WBS3, 
                          CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals'
                           WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName, 
                          (ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND 
                          LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND 
                          LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt
    FROM         PR LEFT OUTER JOIN
                          LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
                          EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
                          LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
                          LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
                          WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
                          WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
    WHERE     (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '') 
                          AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298'
    GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud
    second query
    Code:
    SELECT     WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') 
                          THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0)) 
                          = '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') 
                          THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) 
                          = '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND 
                          (SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') 
                          THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType
    FROM         PR LEFT OUTER JOIN
                          LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
                          EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
                          LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
                          LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
                          WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
                          WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
    WHERE     (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '') 
                          AND pr.wbs1 = '001-333'
    GROUP BY WM_Template.WBS2
    Any suggestions would be helpful.

    Thank You.

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Generically, you could do this:

    SELECT <columns>
    FROM
    <Subquery1> q1
    JOIN
    <Subquery2> q2
    on q1.Key = q2.Key

    It may be more efficient to combine the logic of the two queries but I haven't looked to see how practical that would be.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    May 2004
    Posts
    105
    Thanks for your suggestion it helped me tremendously.

    -Laura

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •