mysql - Output rows with null value only if there isn't the same row with a non-null value

Question

Trying to formulate my question as good as I can...

I have a pricing table with historic data in it. So per item there is a price for certain dates. The difficulty is that the rows also have a type (1 = price for purchase order, 2 = sales order) and a VendorID.

That VendorID can be filled on a row: the price on that row is then for that specific vendor. If there is no row for a certain item in this table that has a VendorID, but it does have a row where VendorID is null, that row should be in the result.

So, if there are two rows in the result, one with a VendorID value and one with the VendorID being null, the row with the value should be in the result set and the row with the null value may not be in the result set.

Also, the result set should only contain the prices that are the newest, so i have to take in account the 'FromDate'.

The name of the column 'VendorID' is not well chosen because the rows with type = 2 are for sales orders, but let's forget about that for now ;-)

If I want all items for type = 1, would like to have the following result set:

ID | ItemID | FromDate   | Type | VendorID | price
------------------------------------------------
1  | 1.     | 2020-01-01 | 1    | 97       | 2.45
9  | 2      | 2020-02-15 | 1    | 97       | 3.88
7  | 3      | 2020-01-01 | 1    | 97       | 2.55

Suppose IDs 3,4 and 9 wheren't in the table (so, no pricing for item 2 for specific VendorID 97), the result should be:

ID | ItemID | FromDate   | Type | VendorID | price
------------------------------------------------
1  | 1      | 2020-01-01 | 1    | 97       | 2.45
13 | 2      | 2020-01-01 | 1    | NULL     | 999.45
7  | 3      | 2020-01-01 | 1    | 97       | 2.55

For ItemID 2 this would mean that there isn't a specific price set for VendorID 97 but there is a general price set (VendorID is null) and this price should now be placed in the result set.

I hope I explained it more clearly now....

I've written loads of queries now and also googled a lot but I cannot find how to make it do what I want. I tried distinct, sorting, but no luck. Must be something simple but I can't find it.

Up until now I have the following Mysql query but a) it outputs both the rows where VendorID is null and where it has value and b) I think its very overcomplicated but can't figure out how to make it simpler and faster.

SELECT I.ItemID, I.Name, b.vendorID, b.max_date, IP.Price, T.Percentage 
FROM Items I    
JOIN ( SELECT ItemID, VendorID, MAX(FromDate) max_date, type 
    FROM ItemPrices 
    WHERE Type = 1 AND FromDate < '2020-02-30' 
           AND VendorID = (SELECT ID 
                         FROM Vendors 
                         WHERE VendorID = 'V001') 
          OR VendorID IS NULL 
   GROUP BY ItemID, VendorID    
   ) b ON I.ID = b.ItemID
JOIN ItemPrices IP ON IP.ItemID = b.ItemID 
                     AND IP.Type = b.type 
                     AND IP.FromDate = b.max_date 
                     AND (IP.VendorID = b.VendorID OR IP.VendorID IS NULL)    
LEFT JOIN TaxCodes T ON T.ID =   
                                ( SELECT TC.TaxCodeID 
                                FROM TaxCombinations TC 
                                WHERE TC.Direction = 1 
                                AND TC.TaxAreaID = (SELECT TaxArea 
                                                    FROM Vendors 
                                                    WHERE ID = (SELECT ID 
                                                                FROM Vendors 
                                                                WHERE VendorID = 'V001') ) 
                                AND TC.ItemTaxID = I.ItemTaxID )    
ORDER BY I.ItemID ASC

Also looked at the following urls but still don't know what to do: Distinct rows with non-null values taking precedence over nulls Select the non-null value if exists else null, but always select the row

Can someone please help me?

Answer 1

If you want to get the newest for each ItemID corresponding to the Type selection, you can make a sub-query to return the newest price first then join the original table to show it in the end output. Below are the example query:

SELECT A.* 
FROM   ItemPrices A 
JOIN   ( SELECT itemid,
                TYPE,
                MAX(fromdate) AS mdt 
         FROM   ItemPrices 
         GROUP BY itemid,TYPE ) B
ON      A.itemid=B.itemid 
        AND A.type=B.type 
        AND A.fromdate=B.mdt
WHERE   A.type=1
ORDER BY A.itemid;

You can view the demo here : https://www.db-fiddle.com/f/7YCaiLYz9DE11wnijWEdi/3