find records from 2 situations?

Hi All, I am trying to find records when searchProductCount = 2 AND when searchProductCount < 2 BUT productID not in (select pid from TableB) ... I have the query below ... but is there any other better way to do this?

TableB has IDs: 100, 700 ...etc

eg: searchProduct ID is 50,100

-- means returns everything when we found ALL productID (50,100) from TableA, count =2

Select productName, productID from TableA where searchProductCount = 2 AND productID IN (50,100)

union all

-- when not all productID found in TableA, we only return productsID from TableA which ID found in TableB, count < 2

Select productName, productID from TableA where searchProductCount < 2 and productID IN (select pid from TableB) -- in this case, pid found in TableB from searchProductID will be 100

--------------------

it comes out there are duplicates results (when first query is valid, we union all second query, so we have duplicates records). How can we eliminate the duplicates? Or is there better way to acheive this without using union all?

>>>>Select productName, productID from TableA where searchProductCount < 2 and productID IN (select pid from TableB) -- in this case, pid found in TableB from searchProductID will be 100

Why would (select pid from TableB) be only 100? It will return every productid from the tableB. Your question is little confusing. Can you post some sample data from each of the tables and expected output..

|||

Select productName, productID from TableA where searchProductCount <2 andproductID IN (select pid from TableB)-- in this case, pid foundin TableB from searchProductID will be 100

because the search id are 50 and 100 ... in above case, we need to return records where search id is in tableB. So in this case, we are looking for :

Select productName, productID from TableA where searchProductCount <2 and productID IN (100)

|||

post some data from Tables A and B and expected output please..