Search code examples
joinkdb

kdb/q What kind of Join would I need for this scenario?

I have two tables like this:

participants: each person in the race, along with their score and the name of race they ran in

Name Score Race
Ann 66 8a
Bob 20 8a
Ann 73 7d
Bob 25 7d

races: the name of each race, and whether it was included in the final tallying score

Race included
8a 0
7d 1

At the end, I want to calculate the scores for each participant for only those races that are included in the final tallying score.

What kind of join would I need to create a combined table that includes the "included" column?

Name Score Race included
Ann 66 8a 0
Bob 20 8a 0
Ann 73 7d 1
Bob 25 7d 1

Thanks for the help.

Solution

  • The response above was close but the where clause was in the wrong place:

    q)select sum Score where included by Name from(participants lj`Race xkey races)
    Name Score
    Ann 73
    Bob 25

    Another way to get the results is by removing the parentheses and adding "= 1" for clarification:

    q)select sum Score where = 1 included by Name from participants lj`Race xkey races