How do I join multiple lookup tables?

[abidgoliwb]

I have couple of lookup tables as follows:
Table 1
A 1
B 5
C 6

Table 2
A one
A two
A three
B one
C one

Trying to lookup so that all the values from Table 1 that are IN table 2 are returned, but I'm only getting the 1st entries. My lookup returns
A 1 one
B 5 one
C 6 one

But I want
A 1 one
A 1 two
A 1 three
B 5 one
C 6 one

Here is the lookup. Appreciate any help.

| inputlookup table 1
| join type=inner [ inputlookup table 2]

[DalJeanis · Accepted solution]

Try this...

| inputlookup table 1 
| join type=inner max=0 [ inputlookup table 2]

There is also a way for an admin to officially set up the second lookup to return multiple results, but the above will work.

[abidgoliwb]

Fantastic. Exactly what I was looking for 🙂

[abidgoliwb]

Follow up question. How can I continue this lookup against a third table but return the results ONLY if they are not present in the third table as follows:

Result of join of first to table
A 1 one
A 1 two
A 1 three
B 5 one
C 6 one

3rd table
B Done
C Done

Return
A 1 one
A 1 two
A 1 three

Thanks

[DalJeanis]

@abidgoliwb - this forum does not work well with "followup questions" that add new items after the question is solved - you will end up waiting for an original answerer who may not log on for days.

It's best to write up the new question, with all the information needed to understand what is being asked, and include a reference to the prior answer.

I believe you are looking for

| inputlookup table1 
| join type=inner max=0 keyfield [ inputlookup table2]
| lookup table3 keyfield OUTPUT keyfield AS foundit
| where isnull(foundit)