Association between two tables

[#1]

Hi all,

I have signupactivity  table in this i have used invitation_id to make association with many other different tables in the app.

Now i need to do association with useractivity table where it consists user_id.

when i used user_id to make association with both the table. I am getting sync table because signupactivity  table has invitation_id which has association with many other table.

how can i achieve association with these two tables without sync table?

signupactivity table:

invitation_id     user_id     email_type   user_signed

useractivity table:

user_id      email_type    status

I need association between these two tables because i need a count email_type.

signupactivity table email_type has types 44,45,46,47

useractivity table email_type has types 2,3,4,8

Now i need to create a pivot table where it shows the count of email_type according to email types in one column like below.

email_type          count

44                       100

45                        97

46                        24

47                        86

2                          15

3                          12

4                           7

8                           9

Please anyone help to achieve this.

Advance thanks,

Regards,

Pramod

[sasiparupudi1]

alias the user_id field

useractivity table:

load user_id as useractivity_id,      email_type,    status

resident your table;

hth

Sasi

[Gysbert_Wassenaar]

how can i achieve association with these two tables without sync table?

Rename fields so they don't have the same name in different tables and won't be used to associate those tables. You can also try using the Qualify keyword to list fields that should be prefixed with the table name to create unique names.

[#4]

Hi Gysbert Wassenaar,

I dint get you.

Please explain with an example or with above example.

Regards,

Pramod

[#5]

If i load user_id as useractivity_id, then how it will link with the signupactivity table.

Please explain it. I am not getting.

Regards,

Pramod

[sasiparupudi1]

they will be joined based on the email type column .. dont you have common values in them?

[#7]

Hi sasidhar,

yes we don't have common values with them.

I have attached my complete load script.

Please check and assist me.

I need the email_type count according to specific client.

email_type is in SIGNUPACTIVITYLOG and USERACTIVITYLOG.

please ask any clarifications needed.

[#8]

Any one help on this issue.

[sasiparupudi1]

can you post your qvw?

[#10]

sorry, I am working in server and i have all data in that. I think not good to post my original data.

Please see the sample data i am attaching.

You can see the email_type_id in the sample data. it is different in both the table.

I need the count of users according to email_type_id.

For example when i filtered through the client_id 1.

I need the count of all the users according to email_type_id on client basis.

You can see in the image, the email_type_id which i have taken from the useractivity table is not listing under the client and batch.

They are listing in top in separate rows.

please help..