Question

Hierarchy-lookup tables, left joins, and null values

  • 30 March 2016
  • 2 replies
  • 100 views

Userlevel 2

I’m using MariaDB (essentially MySQL), and have LookML that looks something like this:



Old LookML
```
- explore: people
joins:
- join: father_lookup
from: parent_lookup
sql_on: (${father_lookup.child} = ${people.id} and ${father_lookup.accuracy_flag})
relationship: many_to_many
fields: []
- join: father
from: people
sql_on: (${father.id}=${father_lookup.parent} and ${father.sex}='male')
relationship: many_to_one
- join: mother_lookup
from: parent_lookup
sql_on: (${mother_lookup.child} = ${people.id} and ${mother_lookup.accuracy_flag})
relationship: many_to_many
fields: []
- join: mother
from: people
sql_on: (${mother.id}=${mother_lookup.parent} and ${mother.sex}='female')
```


New LookML
```
explore: people {
join: father_lookup {
from: parent_lookup
sql_on: (${father_lookup.child} = ${people.id} and ${father_lookup.accuracy_flag}) ;;
relationship: many_to_many
fields: []
}

join: father {

from: people

sql_on: (${father.id}=${father_lookup.parent} and ${father.sex}=‘male’) ;;

relationship: many_to_one

}


join: mother_lookup {

from: parent_lookup

sql_on: (${mother_lookup.child} = ${people.id} and ${mother_lookup.accuracy_flag}) ;;

relationship: many_to_many

fields: []

}


join: mother {

from: people

sql_on: (${mother.id}=${mother_lookup.parent} and ${mother.sex}=‘female’) ;;

}

}


</details>

The `parent_lookup` table has only three columns: `parent`, `child`, and `accuracy_flag`. The first two columns are populated with `people.id` values. The `accuracy_flag` column has values 1 and 0 to indicate accuracy of the information in the row. Each `people.id` can appear any number of times in the `parent_lookup.child` column of `parent_lookup`, and any number of times in the `parent_lookup.parent` column.

I join `people` to itself twice over: to obtain each person's mothers, and to obtain each person's fathers.

A `left join` is what I want (and have) here, because I want to include people even if they have no mother/father (in my table); if I used an `inner join`, I'd include only such people as have both a mother and a father.

But the first `join`, to `parent_lookup`, includes *all* rows where `child=people.id`, both mother rows and father rows, and the next `join`, back to `people` again, restricts to where the `sex` is correct. Thus, in my result sets, I have a lot of `null` values.

For example, if `people` is

id sex
---------
1 male
2 male
3 female
4 female
5 female

and `parent_lookup` is

parent child
------------
2 1
3 1
5 4

then a Looker look listing all `father`s of people who have `3` as a mother will yield

father
------
<null>
2

where the first row comes from `left join`ing to `father_lookup`, finding `2` and `3`, and then `left join`ing on `father` and `null`ing out the `3` row.

I don't want those `null` rows.

If I include `sex` in the `parent_lookup` table and join it on `and sex=...`, then I get rid of the `null`s in the above example, but still have `null`s if I, for example, look for all `father`s of people who have `3` or `5` as a `mother`. (`4` has no `father`, so the `join` yields `null` rows.)

I seek a way (using SQL or LookML) to completely eliminate `null`s from these result sets. I would appreciate any insight/advice.

2 replies

Userlevel 2

Hm, actually, once I include sex in the parent_lookup table, the remaining null values are useful: they indicate the lack of a parent of the kind specified. Including sex in parent_lookup actually is the solution.

Michael,

For each old LookML code block in your article and its comments, we just added the New LookML equivalent code.

Reply