SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 List all records from Where IN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rkruis
Starting Member

Canada
28 Posts

Posted - 07/25/2014 :  11:21:02  Show Profile  Reply with Quote
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
Posting Yak Master

167 Posts

Posted - 07/25/2014 :  11:55:57  Show Profile  Reply with Quote
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
Go to Top of Page

rkruis
Starting Member

Canada
28 Posts

Posted - 07/25/2014 :  14:37:58  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36952 Posts

Posted - 07/25/2014 :  14:50:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MichaelJSQL
Posting Yak Master

167 Posts

Posted - 07/25/2014 :  14:59:59  Show Profile  Reply with Quote
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
Go to Top of Page

rkruis
Starting Member

Canada
28 Posts

Posted - 07/25/2014 :  15:24:31  Show Profile  Reply with Quote
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
Go to Top of Page

MichaelJSQL
Posting Yak Master

167 Posts

Posted - 07/25/2014 :  17:20:21  Show Profile  Reply with Quote
Use an isnull(partnumber,identifier)
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
374 Posts

Posted - 07/28/2014 :  13:38:25  Show Profile  Reply with Quote


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#

Go to Top of Page

MichaelJSQL
Posting Yak Master

167 Posts

Posted - 07/28/2014 :  13:46:32  Show Profile  Reply with Quote
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.
Go to Top of Page

rkruis
Starting Member

Canada
28 Posts

Posted - 07/29/2014 :  11:11:21  Show Profile  Reply with Quote
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++.


Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
374 Posts

Posted - 07/29/2014 :  11:13:36  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000