Reputation: 41
I have a view that when executed returns the count of how many times a code is being referenced. My view is :
Create view [myview] as
SELECT S.id AS code_id,
u.asset_standard_id,
Count(U.pkey) AS COUNT
FROM imtbl_code S LEFT OUTER JOIN
imvw_woap_code_with_cust U ON U.code_id = S.Id and s.code_type = (select pkey from imtbl_code_type where imtbl_code_type.id = 'A-Problem')
GROUP BY S.id,
u.asset_standard_id
order by count
so if my tables are set up like the following :
imvw_woap_code_with_cust || imtbl_code
---------------------------------
Code_id | asset_standard_id || Code_id
----------------------------------------------
c1 25209-45MEO || c1
c3 25209-45MEO || c2
c3 25209-45MEO || c3
when i run select * from myview where asset_standard_id = '25209-45MEO'
the view returns
code_id || asset_standard_id || count
c3 25209-45MEO 2
c1 25209-45MEO 1
what i would like to see is values returned when the count is zero as well.
code_id || asset_standard_id || count
c3 25209-45MEO 2
c1 25209-45MEO 1
c2 25209-45MEO 0
I asked a similar question previously but wasnt clear, just trying to clarify a bit more.
I wanted to add from the answers that this code :
SELECT
possibilities.ID AS code_id,
possibilities.asset_standard_id,
Count(U.pkey) AS [COUNT]
FROM (
SELECT
a.asset_standard_id,b.ID
FROM (SELECT DISTINCT asset_standard_id FROM imvw_woap_code_with_cust) AS a
CROSS JOIN (
SELECT DISTINCT ID FROM imtbl_code
UNION
SELECT DISTINCT CODE_ID AS [ID] FROM imvw_woap_code_with_cust
) AS b
) AS possibilities
LEFT OUTER JOIN imvw_woap_code_with_cust AS U ON U.code_id = possibilities.Id
and u.code_type = (
select pkey
from imtbl_code_type
where imtbl_code_type.id = 'A-Problem'
)
where u.code_type_id = 'A-Problem'
GROUP BY possibilities.id, possibilities.asset_standard_id
was the closed but the counts are way off.
SAMPLE DATA FROM TALBES imvw_woap_code_with_cust
pkey code_id code_type_id, asset_standard_id
101480 Alarmining A-Problem 1 East
103350 Fluid/Gas Leak (Out) A-Problem 2 west
104033 plug/Pwr Cord Damg A-Problem 2 west
107296 Alarmining A-Problem 2 west
106404 Incoming Inspect A-Problem 2 west
106407 Incoming Inspect A-Problem 2 west
IMTBL_CODE
pkey, code_id, code_type_id
100064 1-NO PROBLEM CODE ENTERED A-Problem
100068 Alarmining A-Problem
100069 Battery Failure A-Problem
100070 Incoming Inspect A-Problem
100071 Fluid/Gas Leak (Out) A-Problem
100072 Case Damaged A-Problem
100073 plug/Pwr Cord Damg A-Problem
Upvotes: 0
Views: 100
Reputation: 12804
You need to get all possible combinations. CROSS JOIN works for this, but caution: it is painful on large datasets.
DECLARE @imvw_woap_code_with_cust TABLE (
pkey INT,
Code_id VARCHAR(200),
code_type_id VARCHAR(200),
asset_standard_id VARCHAR(200)
)
INSERT INTO @imvw_woap_code_with_cust(pkey,Code_id,code_type_id,asset_standard_id)
SELECT 101480,'Alarmining','A-Problem','1 East'
UNION SELECT 103350,'Fluid/Gas Leak (Out)','A-Problem','2 west'
UNION SELECT 104033,'plug/Pwr Cord Damg','A-Problem','2 west'
UNION SELECT 107296,'Alarmining','A-Problem','2 west'
UNION SELECT 106404,'Incoming Inspect','A-Problem','2 west'
UNION SELECT 106407,'Incoming Inspect','A-Problem','2 west'
DECLARE @IMTBL_CODE TABLE (
pkey INT,
Code_id VARCHAR(200),
code_type_id VARCHAR(200)
)
INSERT INTO @IMTBL_CODE(pkey,Code_id,code_type_id)
SELECT 100064,'1-NO PROBLEM CODE ENTERED','A-Problem'
UNION SELECT 100068,'Alarmining','A-Problem'
UNION SELECT 100069,'Battery Failure','A-Problem'
UNION SELECT 100070,'Incoming Inspect','A-Problem'
UNION SELECT 100071,'Fluid/Gas Leak (Out)','A-Problem'
UNION SELECT 100072,'Case Damaged','A-Problem'
UNION SELECT 100073,'plug/Pwr Cord Damg','A-Problem'
SELECT
possibilities.code_id AS code_id,
possibilities.asset_standard_id,
Count(U.pkey) AS [COUNT]
FROM (
SELECT
a.asset_standard_id,b.code_id
FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
CROSS JOIN (
SELECT DISTINCT code_id FROM @imtbl_code
UNION
SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust
) AS b
) AS possibilities
LEFT OUTER JOIN @imvw_woap_code_with_cust AS U ON U.code_id = possibilities.code_id
AND possibilities.asset_standard_id=u.asset_standard_id
and u.code_type_id='A-Problem'
GROUP BY possibilities.code_id, possibilities.asset_standard_id
order by [count]
Upvotes: 1