SQL joins with multiple records into one with a default

Question

My 'people' table has one row per person, and that person has a division (not unique) and a company (not unique).

I need to join people to p_features, c_features, d_features on:

people.person=p_features.num_value  
people.division=d_features.num_value  
people.company=c_features.num_value  

... in a way that if there is a record match in p_features/d_features/c_features only, it would be returned, but if it was in 2 or 3 of the tables, the most specific record would be returned.

From my test data below, for example, query for person=1 would return 'FALSE'
person 3 returns maybe, person 4 returns true, and person 9 returns default

The biggest issue is that there are 100 features and I have queries that need to return all of them in one row. My previous attempt was a function which queried on feature,num_value in each table and did a foreach, but 100 features * 4 tables meant 400 reads and it brought the database to a halt it was so slow when I loaded up a few million rows of data.

create table p_features (
        num_value int8,
        feature varchar(20),
        feature_value varchar(128)
);
create table c_features (
        num_value int8,
        feature varchar(20),
        feature_value varchar(128)
);
create table d_features (
        num_value int8,
        feature varchar(20),
        feature_value varchar(128)
);
create table default_features (
        feature varchar(20),
        feature_value varchar(128)
);
create table people (
        person int8 not null,
        division int8 not null,
        company int8 not null
);
insert into people values (4,5,6);
insert into people values (3,5,6);
insert into people values (1,2,6);
insert into p_features values (4,'WEARING PANTS','TRUE');
insert into c_features values (6,'WEARING PANTS','FALSE');
insert into d_features values (5,'WEARING PANTS','MAYBE');
insert into default_features values('WEARING PANTS','DEFAULT');

Answer 1

I don't know if I had understood very well your question, but to use JOIN, you need your table loaded already and then use the SELECT statement with INNER JOIN, LEFT JOIN or whatever you need to show. If you post some more information, maybe turn it easy to understand.

Answer 2

There are some aspects of your schema I'm not understanding, like how to relate to the default_features table if there's no match in any of the specific tables. The only possible join condition is on feature, but if there's no match in the other 3 tables, there's no value to join on. So, in my example, I've hard-coded the DEFAULT since I can't think of how else to get it.

Hopefully this can get you started and if you can clarify the model a bit more, the solution can be refined.

select p.person, coalesce(pf.feature_value, df.feature_value, cf.feature_value, 'DEFAULT')
    from people p
        left join p_features pf
            on p.person = pf.num_value
        left join d_features df
            on p.division = df.num_value
        left join c_features cf
            on p.company = cf.num_value

Answer 3

You need to transpose the features into rows with a ranking. Here I used a common-table expression. If your database product does not support them, you can use temporary tables to achieve the same effect.

;With RankedFeatures As
    (
    Select 1 As FeatureRank, P.person, PF.feature, PF.feature_value
    From people As P
        Join p_features As PF
            On PF.num_value = P.person
    Union All
    Select 2, P.person, PF.feature, PF.feature_value
    From people As P
        Join d_features As PF
            On PF.num_value = P.division
    Union All
    Select 3, P.person, PF.feature, PF.feature_value
    From people As P
        Join c_features As PF
            On PF.num_value = P.company
    Union All
    Select 4, P.person, DF.feature, DF.feature_value
    From people As P
        Cross Join default_features As DF
    )
    , HighestRankedFeature As
    (
    Select Min(FeatureRank) As FeatureRank, person
    From RankedFeatures
    Group By person
    )
Select RF.person, RF.FeatureRank, RF.feature, RF.feature_value
From people As P
    Join HighestRankedFeature As HRF
        On HRF.person = P.person
    Join RankedFeatures As RF
        On RF.FeatureRank = HRF.FeatureRank
            And RF.person = P.person
Order By P.person