Left join not loading

[#1]

I have two tables that I am trying to join.

First one has 3M rows and Second 2.8 M.

Here is the script am trying to use:

QUALIFY*;

UNQUALIFY OPRID;

JANJOIN:

LOAD OPRID,

     ROLENAME,

     CLASSID,

     MENUNAME

FROM JanJoin.txt

QUALIFY*;

UNQUALIFY OPRID;

LEFT JOIN (JANJOIN)

LOAD OPRID,

     ROLENAME,

     CLASSID, y

     MENUNAME

FROM MayJoin.txt

It fetches all rows in 20 seconds and than in freezes up every time I try to load.

Is there something wrong with the script or is it a memory limitation issue?

[sunny_talwar]

Is this a one to one join or is this one to many or many to many join?

[#3]

many to many

[swuehl]

So in worst case, this could lead to a resulting table with record number in the order of 1E12?

(Probably not that much in real case, but may explain why your computer freezes)

What are you trying to achieve? Maybe there is a better solution.

[#5]

I am trying to find out which new users were added between May and Jan (OPRID is a username).

The thing: there is multiple criteria for those users  (e.g. AccountDisabled <>1, etc). This is all stored in different tables.

In order to get that I ran MayJoin and JanJoin queries in Access. These were LEFT JOINS of 4 tables. Hence the number of records.

[swuehl]

But basically, shouldn't you end up with two list of users, one for Jan and one for May, with a single record per user? Why are you coping with multiple records per user? And why are you trying to join the tables?

There are multiple ways to identify new customers, script or front end based, and you can find examples in the forum.

For example:

New customer analysis using set analysis

[#7]

I forgot to mention I also need to do another Join which will give New Roles for Existing Users.

So let's say Each User has multiple Menus and Each Menu has Multiple Roles.

That is why ended up with multiple user rows.

[swuehl]

Ok, probably I over simplified what you are trying to do.

Well, as stated above I think your join will need to much memory / create too many rows.

You can test this by limiting your two tables to a subset of records (e.g. using FIRST ... prefix or a WHERE clause). Maybe like

FIRST 100

LOAD Key, ....

FROM A;

LEFT JOIN

LOAD Key, ....

FROM B

WHERE EXISTS(Key);