mysql - Output row 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

With NOT EXISTS:

select t.* from tablename t
where t.vendorid is not null
or not exists (
  select 1 from tablename
  where itemid = t.itemid and vendorid is not null
)

See the demo.