Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Joining 3 lists

I have 3 lists i have to combine.

In every list i add an extra field in the script to identify from which list the record comes.

For Example :

Table 1 :

Name        T1

A               1

B               1

C               1

D               1


Table 2 :

Name    Code    T2

A             1        1

C             2        1

E             3        1

Table 3 :

Code       T3

1             1

3             1

4             1

After my joins i get 1 table :


Name           Code              T1         T2         T3

A                   1                   1          1           1

B                   -                    1          -            -

C                   2                   1          1           -

D                   -                    1          -            -

E                  3                    -           1           1

-                   4                    -           -             -

Now here is my problem :

How can i get the records of Table 1 that have no relation with Table 2 and Table 3? (same for T2 or T3) so where the value of T1 is 1 and where T2 & T3 have no values.

1 Solution

Accepted Solutions
Luminary Alumni
Luminary Alumni

Hi, here's the solution. I hope it helps.

View solution in original post

7 Replies
Luminary Alumni
Luminary Alumni

What row wuold you like to see as result? 'B'?

Not applicable

In my example yes.

But the best would be if I had 3 listboxs with for example

Listbox T1



That i can select 1 if i want the rows of table 1 and 0 if i want to exclude the rows in Table 1.

But in my table they are null values.

Not applicable

so that i can choose if i want records from Table 1 and Table 2 but not Table 3

or Table 1 but not in Table 2 and Table 3.

Partner - Champion III
Partner - Champion III


Use NullAsValue T1, T2, T3 to make the null values selectable and then create listboxes for T1, T2, T3. Now you can select any combination of values and nulls from your data set.

To choose records in Table 1 and not in table 2 and 3, select 1 for T1, and the null (shows as a blank) in T2 and T3.



Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

So i joined 3 lists in 1 complete list

Then i used the NullAsValue as you proposed but it still doesnt work:

load T1,T2,T3
Resident CompleteList;

So whats wrong with my syntax? Its the first time I use NullAsValue

Luminary Alumni
Luminary Alumni

Hi, here's the solution. I hope it helps.

Not applicable

Yep, I also discovered that i just had to put everything in an other table before it worked.

So its fixed.

But i still find it weird.

So in my CompleteList the Null values were like this :      -

so i did


load *, '1' as Test

resident CompleteList;

drop table CompleteList;

And the '-' values changed in 0 values just by copying the old table and to add a simple field. Really weird

because i never used a If(IsNull())

But it works , so Thanks!

Edit : I used :

Set NullValue = 0;