subject_id not available in all tables?

[chenzhaoyi123]

subject_id appears only available in "subject" table, see below. This variable should be in all tables, thus allow the link between different tables.

[ACharbonneau]

Thanks for your feedback!

We don't have subject_id in all of the tables on purpose. It severely bloats the results and because not all transitive relationships through the database are valid for every query, using cdapython to join across more than two tables at a time often causes false data rows to be created. As such, we limit users to a single join, which we know will be accurate.

So we recommend that if you want to join two tables, you use link_to_table in your query, and our database logic will correctly join any two tables, and will add all the appropriate IDs.

If you want to join 3 or more, we recommend that you run a query for each table you want to join with either link_to_table = "subject" or add_columns = "subject_id" (substitute in whichever table/id field you want to join by), and use pandas to do the further joins. Pandas join functions give you more control over which columns are used, and what type of relationships to accept, so you don't end up with fake data rows.