SQL SELECT Statement - Select record based on number of matches from second table

Question

This is my first question so sorry in advance for breaking any unspoken rules. I currently am trying to retrieve categories from a InventoryCategory table using the primary key from the InventoryItem table.

Since an item can have one or more categories, the InventoryCategory table holds the ItemCode, CategoryCode, and IsPrimary (to define which category is primary category and is TRUE/FALSE type)

I really need to use the main category for all items with only one category and the non-primary category for items with two categories. I am trying to use a CASE statement to output the right value as a named ParentCategory field but having tough time.

Currently, I just have WHERE IC.IsPrimary = 1 since I haven't been able to figure out how to query the ItemCode to return # of CategoryCodes and select non-primary category based on that. I tried doing a COUNT but it always returns 1 and keep striking out.

Code:

SELECT 
    BillToCode, POSWorkstationID, 
    InvoiceDate, ItemName, 
    CategoryCode, 
    SUM(QtySales) QtySales, 
    SUM(Sales) Sales, 
    SUM(QtyReturns) QtyReturns, 
    SUM([Returns]) [Returns], 
    WarehouseCode   
FROM 
    (SELECT 
         CI.BillToCode, CI.WarehouseCode, 
         CI.POSWorkstationID, CI.InvoiceDate, 
         IC.CategoryCode, ii.ItemName, 
         CASE 
            WHEN CI.Type IN ('Invoice', 'Opening Invoice') 
              THEN CID.QuantityShipped 
              ELSE 0 
         END AS QtySales, 
         CASE 
            WHEN CI.Type IN ('Invoice', 'Opening Invoice') 
              THEN CID.ExtPriceRate 
              ELSE 0 
         END AS Sales, 
         CASE 
            WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') 
              THEN CID.QuantityShipped 
              ELSE 0 
         END AS QtyReturns,
         CASE 
            WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') 
              THEN CID.ExtPriceRate 
              ELSE 0 
         END AS [Returns]
     FROM 
         CustomerInvoice CI            
     INNER JOIN 
         Customer C ON CI.BillToCode = C.CustomerCode            
     INNER JOIN 
         CustomerInvoiceDetail CID ON CI.InvoiceCode = CID.InvoiceCode
     INNER JOIN 
         InventoryItem ii ON CID.ItemCode = ii.ItemCode    
     INNER JOIN 
         InventoryCategory IC ON CID.ItemCode = IC.ItemCode
     WHERE 
         (CI.IsBatch = 0 OR CI.IsBatch IS NULL) 
         AND CI.IsPosted = 1 
         AND CI.[Type] = 'Invoice' 
         AND CI.IsVoided = 0 
         AND IC.IsPrimary = 1) Items        
GROUP BY 
    POSWorkstationID, BillToCode, ItemName, InvoiceDate, CategoryCode, WarehouseCode

Answer 1

If the IsPrimary field is 0 for the non-primary row, you could do a SELECT MIN statement. See below:

SELECT BillToCode        
, POSWorkstationID    
, InvoiceDate
, ItemName           
, CategoryCode
, SUM(QtySales) QtySales          
, SUM(Sales) Sales          
, SUM(QtyReturns) QtyReturns          
, SUM([Returns]) [Returns]       
, WarehouseCode   

FROM (          

SELECT CI.BillToCode
, CI.WarehouseCode
, CI.POSWorkstationID
, CI.InvoiceDate
, IC.CategoryCode
, ii.ItemName  
, CASE WHEN CI.Type IN ('Invoice', 'Opening Invoice') THEN CID.QuantityShipped ELSE 0 END AS QtySales            
, CASE WHEN CI.Type IN ('Invoice', 'Opening Invoice') THEN CID.ExtPriceRate ELSE 0 END AS Sales          
, CASE WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') THEN CID.QuantityShipped ELSE 0 END AS QtyReturns          
, CASE WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') THEN CID.ExtPriceRate ELSE 0 END AS [Returns]      

FROM CustomerInvoice CI            
INNER JOIN Customer C ON CI.BillToCode = C.CustomerCode            
INNER JOIN CustomerInvoiceDetail CID ON CI.InvoiceCode = CID.InvoiceCode
INNER JOIN InventoryItem ii ON CID.ItemCode = ii.ItemCode    
INNER JOIN InventoryCategory IC ON CID.ItemCode = IC.ItemCode
WHERE (CI.IsBatch = 0 OR CI.IsBatch IS NULL) 
AND CI.IsPosted = 1 
AND CI.[Type] = 'Invoice' AND CI.IsVoided = 0 
AND IC.IsPrimary = (SELECT MIN(IsPrimary) FROM InventoryCategory ICi WHERE ICi.ItemCode = IC.ItemCode)
) Items        

GROUP BY POSWorkstationID, BillToCode, ItemName, InvoiceDate, CategoryCode, WarehouseCode