List all records from Where IN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

List all records from Where IN

Author

Topic

rkruis
Starting Member

28 Posts

Posted - 2014-07-25 : 11:21:02

I want to be able to return all rows from the IN, even if it was not found.
For example I would like to see,


Part		Count
452430		21
5065153		0
5065524		NOT FOUND
5066285		15544

but currently my query returns missing part 5065524


Part		Count
452430		21
5065153		0
5066285		15544


	select 
		f.[Part Number],
		(	
			select isnull(count(a.applicationID), 0)
			from dbo.PDT_Bulk_Parts ff
			left join dbo.PDT_Bulk_Parts_ACES_Applications a on a.object_identifier = ff.identifier
			where f.[Part Number] = ff.[Part Number]		

		) as 'count'
	
	from dbo.PDT_Bulk_Parts f	
	where f.[Part Number] IN 
		('452430',
		'5065153',
		'5065524',
		'5066285')
	group by f.[Part Number]
	order by f.[Part Number]

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 11:55:57

I'm not sure why you are doing the count in the select. The count is coming from the table dbo.PDT_Bulk_Parts_ACES_Applications and the left side is the same your From clause. You should just be able to use your Count Select statement as your whole statements. This should work

SELECT ff.[Part Number],isnull(count(a.applicationID), 0)as 'count'
FROM dbo.PDT_Bulk_Parts ff
LEFT JOIN dbo.PDT_Bulk_Parts_ACES_Applications a on a.object_identifier = ff.identifier
WHERE ff.[Part Number] IN
('452430',
'5065153',
'5065524',
'5066285')
GROUP BY ff.[Part Number]
ORDER BY ff.[Part Number]

also - you don't need the isnull on the aggregate operation. if not found, you will get 0

rkruis
Starting Member

28 Posts

Posted - 2014-07-25 : 14:37:58

The query you wrote works the same as mine. What I am missing is, if the record is missing. For example if 452430 doesn't exist in ff.[Part Number].
I am looking for a count on a.applicationID but also if a Part Number is not in the database at all, I want to see it.

Does that make sense what I am looking for?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-25 : 14:50:01

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 14:59:59

I see .. You might try a right join and put the (IN clause) as part of your join condition - if you use it as a where clause , it will filter out the data.

SELECT ff.[Part Number],isnull(count(a.applicationID), 0)as 'count'
FROM dbo.PDT_Bulk_Parts ff
RIGHT JOIN dbo.PDT_Bulk_Parts_ACES_Applications a on a.object_identifier = ff.identifier
AND ff.[Part Number] IN
('452430',
'5065153',
'5065524',
'5066285')
GROUP BY ff.[Part Number]
ORDER BY ff.[Part Number]

I hope I understood you corretly

rkruis
Starting Member

28 Posts

Posted - 2014-07-25 : 15:24:31

This works in a way, but returns NULL in the part number which was not found. I want to have the Part number there, and the count can be either null or 0.
This returns
NULL ----- 282518
for the number not found.
I would like to see
452430 ----- 282518

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 17:20:21

Use an isnull(partnumber,identifier)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-28 : 13:38:25

[code]

SELECT
parts.part#, ISNULL(COUNT(a.applicationID), 0) AS applID_count
FROM (
SELECT '452430' AS part# UNION ALL
SELECT '5065153' UNION ALL
SELECT '5065524' UNION ALL
SELECT '5066285'
) AS parts
LEFT JOIN dbo.PDT_Bulk_Parts ff ON
ff.[Part Number] = parts.part#
LEFT JOIN dbo.PDT_Bulk_Parts_ACES_Applications a ON
a.object_identifier = ff.identifier
GROUP BY
parts.part#

[/code]

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 13:46:32

The only bad thing with Scotts approach is you can only account for the part numbers in the subquery/inline view. If those are all the part numbers you will ever care about , then you're good.

If however you don't know what the part numbers will be, you will have to pick a different fields to display as you will not have the part number in you dbo.PDT_Bulk_Parts table and it the attribute itself does not exist in your dbo.PDT_Bulk_Parts_ACES_Applications . So you are left with showing some other attribute . Since there is a relationship on a.object_identifier = ff.identifier, I would show that or join to another look up table that contains a 1:1 mapping between each identifier and the part number.

rkruis
Starting Member

28 Posts

Posted - 2014-07-29 : 11:11:21

Thanks for all the help everyone.
Scott's approach works for what I need on a regular basis. I can build the query and add the part numbers using notepad++.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-29 : 11:13:36

The original code uses an "IN" to restrict the parts to be listed anyway, so only those part numbers will ever be returned by the query.

If, however, for some reason the part numbers if another table had to be listed as well, we could do a UNION of the hard-coded "IN" list and the other table, then LEFT JOIN from that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources