Dark Dark - 5 months ago 7
SQL Question

SQL statement with left joins where clause and a sum()?

Im really struggling with the below SQL, i have tried so many different things and i cant get any of them to work.

What i basically need to do is LEFT OUTER JOIN this working SQL statement...

SELECT "TABLE1"."Sheet Number",
"TABLE1"."ID Number",
"TABLE1"."Identification",
"TABLE1"."Job Date",
"TABLE1"."p2c",
"TABLE2"."Range",
"TABLE1"."bcr",
"TABLE1"."Dataset ID",
"TABLE1"."ACC",
"TABLE1"."GNC",
"TABLE1"."Year",
"TABLE1"."Period",
"TABLE1"."Week",
"TABLE1"."Job_ID"
FROM "DATABASE"."dbo"."TABLE2" "TABLE2" LEFT OUTER JOIN "DATABASE"."dbo"."TABLE1" "TABLE1"
ON (((("TABLE2"."Contract"="TABLE1"."GNC")
AND ("TABLE2"."FromPeriod"="TABLE1"."Period"))
AND ("TABLE2"."FromWeek"="TABLE1"."Week"))
AND ("TABLE2"."FromYear"="TABLE1"."Year"))
WHERE "TABLE1"."ACC"='ACCOUNT57'
AND "TABLE1"."Dataset ID"=5
AND "TABLE1"."bcr"=1
AND "TABLE2"."Range"='Week'
ORDER BY "TABLE1"."Sheet Number"


with this one ...

SELECT "SALES"."JobId",
"SALES"."Total",
SUM("SALES"."Total") AS JOBTOTAL
FROM "DATABASE"."dbo"."SALES" "SALES"
GROUP BY "SALES"."JobId"
ON "SALES"."JobId"="TABLE1"."Job_ID"


But the other joins are causing me a great deal of confusion/frustration when trying to implement solutions ive found online, Ive read that it may be the WHERE and GROUP BY statements not playing nice??

But i cant seem to get any solution i find online to work. One solution i tried it looked like they pretty much just wrapped the SQL statement with the SUM() and GROUP BY (my bottom statement) inside brackets and joined that?

Any one out there able to help?

Thanks for your time.

Answer
SELECT
    t1.Sheet Number,
    t1.ID Number,
    t1.Identification,
    t1."Job Date",
    t1.p2c,
    t1."Range",
    t1.bcr,
    t1.Dataset ID,
    t1.ACC,
    t1.GNC,
    t1."Year",
    t1.Period,
    t1."Week",
    t1.Job_ID,
    SUM(s.Total) AS JOBTOTAL
FROM    
    dbo.TABLE1 t1
    INNER JOIN dbo.TABLE2 t2
       ON t1.GNC = t2.Contract
       AND t1.Period = t2.FromPeriod
       AND t1."Week" = t2.FromWeek
       AND t1."Year" = t2.FromYear
       AND t2."Range"='Week'
    LEFT JOIN dbo.Sales s
       ON t.Job_Id = s.Job_id
WHERE
    t1.ACC='ACCOUNT57' 
    AND t1.Dataset ID=5 
    AND t1.bcr=1 
GROUP BY
    t1.Sheet Number,
    t1.ID Number,
    t1.Identification,
    t1."Job Date",
    t1.p2c,
    t1."Range",
    t1.bcr,
    t1.Dataset ID,
    t1.ACC,
    t1.GNC,
    t1."Year",
    t1.Period,
    t1."Week",
    t1.Job_ID
ORDER BY
    t1.Sheet Number

This is a little bit of a guess because your query doesn't make since to me. I assume that you actually want to limit the results of Table1 with a join to Table2 because you do not actually use any of table2's columns. Which would mean that you are looking to use an INNER JOIN not an OUTER.

Also if your table is named table1 you don't need to use table1 as an alias, try shortening it to t1 or something or just leave off the alias. Including all of the double quotes makes it harder to read your code. And you don't need the parenthesis in your on condition because you are using AND statements so you are not separating precedence for anything.

Lastly I don't know what you have read about where and group statements online but there is no such thing. Either the where includes the records you intend to or your where statement is not correct. Group by simply performs aggregation on your records after your where statement identifies what you want to be aggregated.