使SQL记录从同一个表中的一个或多个记录继承值

Question

Background

I have a MySQL table for which each record represents a region- and/or platform-specific version of an item. For any given item, there will be several versions; there's no primary key and mostly indexed columns.

I start with worldwide records, one for each platform-version of the item. Then I add records for any region-specific values, then add records for any country-specific values. The thing is that I only plan to add values that are unique to that region or country; in other words, all records are going to have null values because I don't want to enter repeated values, so I want records to inherit values from other records.

item | platform | region | country | date       | price | [...]
1    | 1        | [WW]   | null    | 2013-04-01 | 100   |
1    | 2        | [WW]   | null    | 2013-04-01 | 100   |
1    | null     | [EU]   | null    | 2013-04-20 | 80    |
1    | null     | [UK]   | null    | null       | 70    |

I plan to use PHP to display the relevant records for a given country. The thing is, I want to be able to combine/inherit values from that country's region record and the worldwide record. So the UK would have two total records: each one inheriting a platform value from the [WW] record, both inheriting the date value from [EU] record, and both having the price value from the [UK] record.

1 | 1 | [UK] | 2013-04-20 | 70
1 | 2 | [UK] | 2013-04-20 | 70

The question I want to know is there a solution/procedure/method of doing it in MySQL only? Or is the only way to do it is via PHP coding?

Answer 1

What you've requested

Please note this is NOT a real answer. It only outputs what you've asked in the question, but the logic here barely makes any sense so it is highly unlikely to be applicable for a real database.

SELECT a.item, b.platform, a.region, a.country, c.date, a.price FROM
    (SELECT item, region, country, price FROM table WHERE platform IS NULL AND date IS NULL GROUP BY item) AS a
    LEFT JOIN 
        (SELECT platform FROM table WHERE platform IS NOT NULL) AS b 
    ON a.item = b.item 
    LEFT JOIN 
        (SELECT date FROM table WHERE PLATFORM IS NULL AND date IS NOT NULL) AS c 
    ON a.item = c.item 

Better Answer Here

A more organized and perhaps easier way (and still efficient if you don't go up more than 2 layers of parents) would be:

 id | parent_id | item | platform | region | country | date       | price | [...]
 1  | null      | 1    | 1        | [WW]   | null    | 2013-04-01 | 100   |
 2  | null      | 1    | 2        | [WW]   | null    | 2013-04-01 | 100   |
 3  | 1         | 1    | null     | [EU]   | null    | 2013-04-20 | 80    |
 4  | 2         | 1    | null     | [UK]   | null    | null       | 70    |

SELECT items.*, 
parent_items.platform AS pa_platform, parent_items.region AS pa_region, parent_items.country AS pa_country, parent_items.date AS pa_date, parent_items.price AS pa_price,  
grandparent_items.platform AS gpa_platform, grandparent_items.region AS gpa_region,  parent_items.country AS gpa_country, parent_items.date AS gpa_date, parent_items.price AS gpa_price
FROM items
LEFT JOIN 
    items AS parent_items
ON items.parent_id = parent_items.id
LEFT JOIN 
    items AS grandparent_items
ON parent_items.parent_id = grandparent_items.id

Then you have the choice of either using app level logic to display the closest non-empty value:

$region = $result['region'] ? $result['region'] : ($result['pa_region'] ? $result['pa_region'] : $result['gpa_region']);

or you can modify the above SQL to chose the first non-null value:

SELECT COALESCE(items.region, parent_items.region, grandparent.region) AS region, COALESCE(items.platform, parent_items.platform, grandparent.platform) AS platform, ...

Now... If you are actually going to add rows with dependencies

Why not simply make different tables?

Suppose you'll have a price for each region, each platform, each country, and you know the order of precedence (let's say as an example region > country > platform):

Why not make a base table (tbl_platform) with fields id/item/platform/date/price

then a country table (tbl_country) with fields id/platform_id/date/price

then a region table (tbl_region) with fields id/country_id/date/price

If you want the base info, just grab it directly from the base table, and if you want the region info, join the region to the country, then to the base.