Select Query - Coalesce - Case Help

[dleewms]

Hi,

I am using Microsoft SQL Server 2005. I need help with a select statement. I think I may need to include the Case When or Coalesce functions, but I'm not sure. I need to query a table for only 3 fields: Product ID, Bin and VendID. However, each product can be in multiple bins and have a different vendors or no vendor for a particular bin. I want to look in two bins 35 & 49. If there is a vendor associated with bin 49 then I want the product, bin (49) and the vendor for bin 49. However, if there is no vendor on bin 49, I want to pull the vendor for bin 35. If no Vendor is in bin 49 nor 35, bin 49 should be in the result set with NULL for the vendID.

Here is an example

Product Bin VendID
1479 35 Acme
1479 49 Ross
2035 49 Null
2035 35 Carter
1738 35 Null
1738 49 Null
1738 57 Ross

Here are the results I would like when selecting Product, Bin & Vendor

Product Bin VendID
1479 49 Ross
2035 35 Carter
1738 49 Null



The select statement may go something like this, but it is bringing back a row for both bins.

SELECT ProductID, Bin, Vendor
FROM Invt
WHERE Bin in (49, 35)

Thank you in advance.

 

[markros]

;with cte as (Select Product, Bin, case when Bin = 49 then VendID end as Vend49, case when Bin = 35 then VendID end as Vend35 from Invt where Bin in (49,35))

select Product, max(case when Vend49 is null and Vend35 is null then 49 when Wend49 Is null then 35 else 49 end) as Bin,
max(case when Vend49 is null and Vend35 is null then null when Wend49 Is null then Wend35 else Wend49 end) as Vend from cte group by Product

from the top of my head.

PluralSight Learning Library

 

[PDreyer]

select a.Product
,Bin=case when a.VendID is null 
     then case when b.VendID is null 
          then a.Bin 
          else b.Bin end 
     else a.bin end 
,coalesce(a.VendID,b.VendID)
from Invt a
left join Invt b
  on a.Product=b.Product
where a.Bin=49
  and b.Bin=35

 

[dleewms]

Thanks Markros and PDreyer for your responses.

After testing both responses, PDreyer, your solution gets me very close. However, all of the records are not returned. For example, if there is no entry in the Invt table for Bin 49, but there is one for Bin 35, I need a record returned for the product, Bin 35 and the vendID. Below is an updated example.

Product Bin VendID
1479 35 Acme
1479 49 Ross
2035 49 Null
2035 35 Carter
1738 35 Null
1738 49 Null
1738 57 Ross
1925 35 Martin and Sons

Here are the results I would like when selecting Product, Bin & Vendor
Product Bin VendID
1479 49 Ross
2035 35 Carter
1738 49 Null
1925 35 Martin and Sons

Thank you!

 

[markros]

Instead of using LEFT JOIN, try using FULL OUTER JOIN in PDreyer's solution - BTW, a nice one.

Mine solution should have worked too, I think, even if I wrote it from the top of my head.

PluralSight Learning Library

 

[gmmastros]

You'll also need to change the where clause because it will cause the query to act like an inner join. So, in addition to changing the LEFT JOIN to a FULL JOIN, change the WHERE to an AND so that the a.bin and b.bin conditions are part of the join instead of the where.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dleewms]

Thanks again for the great suggestions.

With PDreyer's solution, I changed the LEFT JOIN to a FULL JOIN and moved the where clause to the Join line. However, all combination of records were returned.

Markros, I went back to your code and this time got it to work. Not sure what I did wrong the first time.

Nevertheless, thanks again everybody.