LEFT OUTER JOIN not working
-
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateI cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work. Anything stupid I am doing? tblStates is szState nvarchar(2) tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4) Thx Mark Jackson UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
-
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateI cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work. Anything stupid I am doing? tblStates is szState nvarchar(2) tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4) Thx Mark Jackson UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
-
It looks like there is a sum operation, so whould that not be the aggregate function? :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateI cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work. Anything stupid I am doing? tblStates is szState nvarchar(2) tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4) Thx Mark Jackson UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
Not sure, but you may also have to include in your where clause something that allows TA_START and TA_END to be NULL.
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
I am trying to use a join to pull all 50 states from a table and data from another table if there is anything there. Here is the code
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateI cannot for the life of me get the 50 states to list. I have changed the join order (using tblOutages then tblStates with RIGHT JOIN) but cannot get the join to work. Anything stupid I am doing? tblStates is szState nvarchar(2) tblOutages is CAP_OFFLINE FLOAT, UNIT_STATE nvarchar(4) Thx Mark Jackson UPDATE - THE WHERE clause is causing the issue. Removing it causes all 50 states to show. I tried to put it in a HAVING clause but I can't do that unless I include it in the result set.
Changing the WHERE clause to be part of the JOIN clause produced the desired results
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
AND NOT(O.TA_START > '11/23/2011')
AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateThx Mark Jackson
-
Changing the WHERE clause to be part of the JOIN clause produced the desired results
SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
AND NOT(O.TA_START > '11/23/2011')
AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szStateThx Mark Jackson
The WHERE clause was causing the LEFT OUTER JOIN to act as an INNER JOIN, hence you lost all of the records where there wasn't a match. Well done on finding the right way to do it and for showing others what you did!