How to map results of 2 searches using Join?

[dasaed]

as a result of an inputlookup, I have the following table 1(a dish that a chef can prepare, and the chef's name):

dish	chef
a	gordon ramsay
b	gordon ramsay
c	Guy Fieri
d	Guy Fieri
f	Jamie Oliver
g	gordon ramsay
h	gordon ramsay Rachael Ray

and I have the following table from another outputlookup (the restaurant where a chef works, and the chef name):

restaurant	chef
1	gordon ramsay
2	Guy Fieri
3	Guy Fieri
4	Jaime Oliver
5	Michael Caines

I want to combine the 2 tables into this:

restaurant	dish	chef
1	a	gordon ramsay
1	b	gordon ramsay
2	c	Guy Fieri
2	d	Guy Fieri
3	c	Guy Fieri
3	d	Guy Fieri
4	f	Jamie Oliver
1	g	gordon ramsay
1	h	gordon ramsay
5	null	Michael Caines
null	h	Rachael Ray

Basically, based on tables 1 & 2, how do I get a table telling me the restaurant where a chef works, the dishes that he/she would prepare, and the chef's name?

In stuff I've tried,  I'm able to combine table 1 & 2 with the join command, but a lot of results end up getting filtered out. (eg. I might end up with one result per chef but not getting all the dishes, or one result per dish but not getting all the restaurants).  

[bowesmana · Accepted solution]

Your spelling of Jamie Oliver in your restaurants is wrong - "Jaime" and there is no Rachel Ray in dishes for dish h.

However, this will given you the table - not exactly the same order

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| stats values(*) as * by restaurant chef 
| mvexpand dish
| table restaurant dish chef

this also, with ordering more as you have in your table

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| mvexpand restaurant
| eventstats count(dish) as dishes by chef
| where dishes=0 OR isnotnull(dish)
| table restaurant dish chef dishes

[dasaed]

just wanna say thanks. Your solution worked for my case 🙂