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.
Best Answer
A single row then cannot have a user_id and address_id that do not appear on a row together in the address table.
Yes, I would say you want such a constraint.