Outer Join or SubQuery

Question

Outer Join or SubQuery

Joseph Henry

SSChasing Mays

Points: 626
More actions
September 10, 2010 at 8:02 am

#226799

Hello All:
I'm writing a query to get a count of states listed in a given table based on a single field of criteria. The final result should be list of states with a count for each state and null for states that don't exist. To me this should be a simple outer join. I tried this and it works using a right outer join to a view that contains the criteria. This approach does not work for me b/c I will need to pass criteria to the query/stored procedure.
Working SQL with view:
SELECT tblStates.strStAbb, StateTest.StateCount
FROM StateTest RIGHT OUTER JOIN
tblStates ON StateTest.txtJC = tblStates.strStAbb
ORDER BY tblStates.strStAbb
View with Criteria:
SELECT TOP (100) PERCENT txtJC, COUNT(txtJC) AS StateCount
FROM dbo.tblMO
WHERE (txtMode = 'V')
GROUP BY txtJC
ORDER BY txtJC, statecount
Please let me know if I can give any further explanation. Any help/insight that can be given is greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Can you please post the table definitions, which criteria is going to potentially be passed, and some sample data? Also a quick mock-up of expected results would help us to give you a more accurate answer. This is definitely doable, but I don't want to give you the wrong code without being sure what you need.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

This is a stab in the dark given not much to go on. I just assume you want a list of all states with the count of detail records for each state and if the count is 0 you want null for the count.

select s.StateCode, case when COUNT(d.StateCode) = 0 then null else COUNT(d.StateCode) end

from States s

left join DetailsTable d on d.StateCode= s.StateCode

group by s.StateCode

order by s.StateCode

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

drew.allen SSC Guru Points: 76988 More actions · Answer 3

You just need to apply your filter criteria before or as part of the join statement. You can use a CTE to filter the records before the join or a subquery.

WITH CTE FILTERED AS (

SELECT *

FROM Table1

WHERE <criteria based on parameters>

)

SELECT *

FROM Table1

LEFT OUTER JOIN Table 2

ON Table1.Field = Table2.Field

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 4

All:

I'm still trying to get a handle on this. My apologies for not being more specific.

I'm including code to create the necessary tables. HTH. Again, I'm sorry for not doing this earlier. Any further help is greatly appreciated.

use tempdb

IF OBJECT_ID ('TempDB..#tblStates','U') IS NOT NULL

DROP TABLE #tblStates

IF OBJECT_ID ('TempDB..#tblStatesCrit','U') IS NOT NULL

DROP TABLE #tblStatesCrit

CREATE TABLE #tblStates

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

State NVARCHAR(3)

)

CREATE TABLE #tblStatesCrit

(

lngMOnum int IDENTITY(1,1) NOT NULL,

txtJC NVARCHAR(3),

[txtMode] [nvarchar](2)

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #tblStatesCrit ON

--===== Insert the test data into the test table

INSERT INTO #tblStatesCrit (lngMOnum, txtJC, txtMode)

SELECT '3','AL','V' UNION ALL

SELECT '4','AK','V' UNION ALL

SELECT '5','AZ','A' UNION ALL

SELECT '6','AR','V' UNION ALL

SELECT '7','CA','V' UNION ALL

SELECT '8','CO','A' UNION ALL

SELECT '9','CT','V' UNION ALL

SELECT '10','DE','V' UNION ALL

SELECT '11','DC','A' UNION ALL

SELECT '12','FL','V' UNION ALL

SELECT '13','GA','V' UNION ALL

SELECT '14','HI','A' UNION ALL

SELECT '15','ID','V' UNION ALL

SELECT '16','IL','V' UNION ALL

SELECT '17','IN','A' UNION ALL

SELECT '18','IA','V' UNION ALL

SELECT '19','KS','V' UNION ALL

SELECT '20','KY','A' UNION ALL

SELECT '21','LA','V' UNION ALL

SELECT '22','ME','V' UNION ALL

SELECT '23','MD','A' UNION ALL

SELECT '24','MA','V' UNION ALL

SELECT '25','MI','V' UNION ALL

SELECT '26','MN','A' UNION ALL

SELECT '27','MS','V' UNION ALL

SELECT '28','MO','V' UNION ALL

SELECT '29','MT','V' UNION ALL

SELECT '30','NE','A' UNION ALL

SELECT '31','NV','V' UNION ALL

SELECT '32','NH','V' UNION ALL

SELECT '33','NJ','A' UNION ALL

SELECT '34','NM','V' UNION ALL

SELECT '35','NY','V' UNION ALL

SELECT '36','NC','A' UNION ALL

SELECT '37','ND','V' UNION ALL

SELECT '38','OH','V' UNION ALL

SELECT '39','OK','A' UNION ALL

SELECT '40','OR','V' UNION ALL

SELECT '41','PA','V' UNION ALL

SELECT '42','RI','A' UNION ALL

SELECT '43','SC','V' UNION ALL

SELECT '44','SD','V' UNION ALL

SELECT '45','TN','A' UNION ALL

SELECT '46','TX','V' UNION ALL

SELECT '47','UT','V' UNION ALL

SELECT '48','VT','A' UNION ALL

SELECT '49','VA','V' UNION ALL

SELECT '50','WA','V' UNION ALL

SELECT '51','WV','A' UNION ALL

SELECT '52','WI','V' UNION ALL

SELECT '53','WY','V' UNION ALL

SELECT '54','MT','A' UNION ALL

SELECT '55','MT','V'

--Insert into #tblStates

INSERT INTO #tblStates (State)

SELECT 'AL' UNION ALL

SELECT 'AK' UNION ALL

SELECT 'AZ' UNION ALL

SELECT 'AR' UNION ALL

SELECT 'CA' UNION ALL

SELECT 'CO' UNION ALL

SELECT 'CT' UNION ALL

SELECT 'DE' UNION ALL

SELECT 'DC' UNION ALL

SELECT 'FL' UNION ALL

SELECT 'GA' UNION ALL

SELECT 'HI' UNION ALL

SELECT 'ID' UNION ALL

SELECT 'IL' UNION ALL

SELECT 'IN' UNION ALL

SELECT 'IA' UNION ALL

SELECT 'KS' UNION ALL

SELECT 'KY' UNION ALL

SELECT 'LA' UNION ALL

SELECT 'ME' UNION ALL

SELECT 'MD' UNION ALL

SELECT 'MA' UNION ALL

SELECT 'MI' UNION ALL

SELECT 'MN' UNION ALL

SELECT 'MS' UNION ALL

SELECT 'MO' UNION ALL

SELECT 'MT' UNION ALL

SELECT 'NE' UNION ALL

SELECT 'NV' UNION ALL

SELECT 'NH' UNION ALL

SELECT 'NJ' UNION ALL

SELECT 'NM' UNION ALL

SELECT 'NY' UNION ALL

SELECT 'NC' UNION ALL

SELECT 'ND' UNION ALL

SELECT 'OH' UNION ALL

SELECT 'OK' UNION ALL

SELECT 'OR' UNION ALL

SELECT 'PA' UNION ALL

SELECT 'RI' UNION ALL

SELECT 'SC' UNION ALL

SELECT 'SD' UNION ALL

SELECT 'TN' UNION ALL

SELECT 'TX' UNION ALL

SELECT 'UT' UNION ALL

SELECT 'VT' UNION ALL

SELECT 'VA' UNION ALL

SELECT 'WA' UNION ALL

SELECT 'WV' UNION ALL

SELECT 'WI' UNION ALL

SELECT 'WY'

--Query with criteria

SELECT txtJC, COUNT(txtJC) AS StateCount FROM #tblStatesCrit

WHERE txtMode = 'V'

GROUP BY txtJC

--I would like to do an outer join to #tblStates that shows a null StateCount for States that do

--not meet the criteria

-- Sample Output would be:

AK1

AL1

AR1

AZNULL

CA1

CONULL

CT1

DCNULL

DE1

FL1

GA1

HINULL

IA1

ID1

IL1

INNULL

KS1

KYNULL

LA1

MA1

MDNULL

ME1

MI1

MNNULL

MO1

MS1

MT2

NCNULL

ND1

NENULL

NH1

NJNULL

NM1

NV1

NY1

OH1

OKNULL

OR1

PA1

RINULL

SC1

SD1

TNNULL

TX1

UT1

VA1

VTNULL

WA1

WI1

WVNULL

WY1

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

This is the same thing I posted earlier but using your column and table names. 😀

select State, case when COUNT(txtJC) = 0 then null else COUNT(txtJC) end as StateCount

from #tblStates s

left join #tblStatesCrit sc on sc.txtJC = s.State and sc.txtMode = 'V'

group by State

order by State

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

Or same approach using CTE..posting it because I already had it written before I saw the previous reply 🙂

;WITH Counts AS (

SELECT txtJC as [state], COUNT(*) as [Count] FROM #tblStatesCrit

WHERE txtMode = 'V'

GROUP BY txtJC

)

SELECT st.STATE, C.[count]

FROM #tblStates st

LEFT OUTER JOIN Counts C ON C.state = st.state

ORDER BY State

Joseph Henry SSChasing Mays Points: 626 More actions · Answer 7

Both methods work great. Thanks guys. I have never used Common Table Expressions. So that is something I will look into a little further.

Thanks again:satisfied: