Sql query in SQL server 2008

That doesn't make a lot of sense: You don't have a ProductID in both tables, you have a ProductName - which is very inefficient as well as complicating what you are doing.
Try these tables:

ProductID	ProductName	Count
1	        A         	2
2	        B         	3
3	        C         	1
4	        D         	2
5	        E         	1

ProductID	ItemsSold
1	        1
2	        2
3	        0
4	        1
5	        1

And this query:

SELECT a.ProductName, b.ItemsSold FROM table1 a
JOIN table2 b ON a.ProductID = b.ProductID
WHERE b.ItemsSold > 0

That gives you what I think you are looking for:

ProductName	ItemsSold
A         	1
B         	2
D         	1
E         	1