MySQL – Column value depends on values from two different tables

There are a couple of ways you could separate the current values from the historic ones: you could simply include a boolean field that is true for the latest price and filter on that, or you could keep current prices in a separate table. Both options involve a little extra work to maintain integrity but would make a query for current prices more efficient. This doesn't remove the need for the sub-query to pick the first row that matches the user/group/NULL condition, though fewer rows will be scanned in processing both the inner and outer queries.

The following might be more efficient as a straight query if only the current prices are in the base table (add the is_latest=true check or your current date filter to the where and on clauses if you keep the historic values in the same table):

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   base_prices AS nullmatch
LEFT OUTER JOIN 
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = @GroupIDHere
LEFT OUTER JOIN 
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = @UserIDHere
WHERE nullmatch.referent_id IS NULL

This will only work if you always have a row for each product where referent_id is null, otherwise you could try:

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   users
CROSS JOIN 
       base_prices AS nullmatch
LEFT OUTER JOIN 
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = users.groupid
LEFT OUTER JOIN 
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = users.id
WHERE users.id = @UserIDHere
AND   nullmatch.referent_id IS NULL

In both cases be careful using this construct in a view because further filtering by the fields brought out via those case statements will not be able to use any indexes you may have defined.

Obviously the case statement per field is a bit of faf but it should stop the query runner searching the table once for every row returned as it might with the sub-query arrangement.

In other databases you might be able to use windowing functions (like ROW_NUMBER) to do this more conveniently and efficiently, but mySQL does not support those to my knowledge.

Build your database in stages. You have, so far, three main entities: stores, suppliers, items. The relationship between suppliers and items seems simple enough. A supplier may supply many items and an item may be available through many suppliers. This implies an intersection/junction table between Suppliers and Items. As part of this relationship, the default price, the price in effect if no other arrangements are in effect, can be included.

create tabel SupplierItem(
    SupplierID   int not null,
    ItemID       int not null,
    DefaultPrice currency not null,
    constraint   PK_SupplierItem primary key( SupplierID, ItemID ),
    constraint   FK_SupplierItem_Supplier foreign key( SupplierID )
        references Suppliers( ID ),
    constraint   FK_SupplierItem_Item foreign key( ItemID )
        references Items( ID )
);

Now you have two ways that prices may be adjusted. But let's introduce a third discount simply because it is so common -- the volume-based discount. That can be seen as an attribute of the relationship between Supplier and Item.

create table VolumeDiscount(
    SupplierID   int not null,
    ItemID       int not null,
    MinVol       int not null,
    Discount     tinyint not null check( Discount between 0 and 99 ),
    constraint   PK_VolumeDiscount primary key( SupplierID, ItemID, MinVol )
);

I've omitted the FK definitions that are the same as already defined. Discount is a percentage reduction in price based on a volume >= MinVol and < the next highest MinVol listing. Discount might better be defined as a floating point value -- but this is, after all, for illustration only.

Now let's take the optional geographically-based discounts. This suggests yet another intersection table. It also assumes a table of geographic locations.

create table GeoDiscount(
    SupplierID   int not null,
    GeoID        int not null,
  -- ItemID       int not null,
    Discount     tinyint not null check( Discount between 0 and 99 ),
    constraint   PK_GeoDiscount primary key( SupplierID, GeoID[, ItemID] ),
    constraint   FK_GeoDiscount_Geo foreign key( GeoID )
        references GeoLocations( ID )
);

As you can see, this is easily extendable if there are different discounts for each item, though I don't see that needed very often.

Finally, there are discount agreements between some suppliers and some stores. This can get tricky in that a supplier can give a store a blanket discount for all its items and/or individual discounts on certain items.

create table StoreDiscounts(
    StoreID     int not null,
    SupplierID  int not null,
    ItemID      int,
    Discount    tinyint not null check( Discount between 0 and 99 ),
    constraint  FK_StoreDiscounts_Store foreign key( StoreID )
        references Stores( ID ),
    constraint  UQ_StoreDiscounts unique( StoreID, SupplierID, ItemID )
);

Notice that the natural key (StoreID, SupplierID, ItemID) is defined with a unique constraint rather then a primary key constraint. That is because ItemID is nullable. If ItemID is NULL, the discount applies to all item from the supplier bought by the store. Instead, or in addition, the supplier may offer discounts on a per-item basis. Or one general discount for all items (ItemID is NULL) but different discounts on a select subset of items. In the last case, the per-item discounts may be added to the general discount or it may replace the general discount. There is nothing intrinsic to the design that requires or emphasizes one or the other. Which scheme to use will have to be documented. Whether these discounts replace or are added to the standard volume discounts would be another business rule to be determined outside of this schema design.

This is an untested design, of course, and that last table can probably be improved upon. But the design is fairly "tight" as far as data integrity is concerned and is trivially normalized to BCNF. The queries to work this design will, as may be obvious, fairly complex but that will always be the cost of such desired flexibility. Your job is to make the user's job easier, not your own.