Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can data table do a left join for 3 or more data tables?

I've searched for an answer to this simple question, but can't find a similar question. I have 3 data tables:

set.seed(0)
demo <- data.table(id = 1:10, demo.var = rnorm(10), key = 'id'); demo
lab <- data.table(id = 1:7, tc = rnorm(7), key = 'id'); lab
anthro <- data.table(id = 4:9, bmi = rnorm(6), key = 'id'); anthro

All IDs that are in lab and anthro are in the demo data.table, but lab and anthro contain different subsets of the IDs in demo

Both

lab[demo]
anthro[demo]

give the information I want: all 10 IDs with additional information from either the lab or anthro data.table, but is there a was to merge all 3 together in a similar manner? I've tried some permutations such as

anthro[lab][demo]

but this gives the preserves the anthro information only for the IDs that are in the lab data.table - there's no anthro information for IDs 8 and 9

Thanks in advance for any help

like image 945
David F Avatar asked Jan 28 '14 17:01

David F


People also ask

Can we join more than 3 tables?

Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.

How join multiple tables with LEFT join?

Syntax For Left Join:SELECT column names FROM table1 LEFT JOIN table2 ON table1. matching_column = table2. matching_column; Note: For example, if you have a left table with 10 rows, you are guaranteed to have at least 10 rows after applying join operation on two tables.

Can we use join for more than 2 tables?

We use multiple tables joins to combine data from more than two tables. The join operator is used multiple times to join multiple tables in SQL, as for each new table, one join is added.


1 Answers

anthro[lab[demo]]
#      id        bmi         tc     demo.var
#   1:  1         NA  0.7635935  1.262954285
#   2:  2         NA -0.7990092 -0.326233361
#   3:  3         NA -1.1476570  1.329799263
#   4:  4 -0.8919211 -0.2894616  1.272429321
#   5:  5  0.4356833 -0.2992151  0.414641434
#   6:  6 -1.2375384 -0.4115108 -1.539950042
#   7:  7 -0.2242679  0.2522234 -0.928567035
#   8:  8  0.3773956         NA -0.294720447
#   9:  9  0.1333364         NA -0.005767173
#  10: 10         NA         NA  2.404653389

The inner table is always the one the outer join is performed on, so this nesting makes sure that the table with the super set of the index values is always the inner table.

like image 182
BrodieG Avatar answered Oct 02 '22 05:10

BrodieG