Connections between tables

[#1]

Hi,

I have three tables that I want to join in a good way.

Table 1: purchase (purchaseID, customerID) -> customerID is a unique number to identify a customer

Table 2: customer (customerID, customerID_2) -> one customer could have many customerID, therefor a customerID_2 could have many customerID

Table 3: account (accountID, customerID_3) -> customerID_3 contains both customerID and customerID_2, and one accountID could only have one customerID_3

What I want to do now is to find out all purchaseID connected to a accountID. How can I solve this the best way using customer as a connection table. The best thing would be if instead putting everything in one table, have the three tables separeted with connections between them.

Thanks ahead!

[montubhardwaj]

Please try the below:

[#3]

Hi,

It did work a bit, but im missing some accountID.

Just to clarify;

- all purchaseID have one customerID

- one customerID_2 could have many customerID, these are not shown in the purchase table

- in the account table one accountID have a connection to either a customerID or a customerID_2, these are stored as customerID_3

When I use your script I only get the accountID which have a connection to a customerID but not to those accountID which have the customerID_2 in the column customerID_3.

Is it possible to have these three tables separeted and not joined in only one table, because there is other columns in each tables that we want to use.

Thanks ahead!

[montubhardwaj]

ok... So if I am getting you right, you key field should be this way so that it will cover all the Customer Ids.

purchase:

Load purchaseID as ID,

customerID_Pur

From purchase_table;

customer:

Left join

Load customerID as ID,

customerID_2_Cust

From customer_table;

account:

Left join

Load accountID as ID,

customerID_3 _Acct

From account_table;

If it is not working, maybe I got your requirements wrongly.

[#5]

Sorry, maybe Im not clear enough . This is how it looks like now.

purchase:

Load

     purchaseID,

     customerID

From purchase_table;

customer:

Load

     customerID,

     customerID_2

from customer_table;

account:

Load

     accountID,

     customerID_3

From account_table;

1) one purchaseID has only one customerID

2) one customerID can only be connected to one customerID_2 or null

3) one accountID can have many purchaseID

4) one accountID has a connection to either one customerID or customerID_2 not both, and that ID is stored as customerID_3

Problem 1 is that we don't know what customerID is connected to what customerID_2 throught the purchase_table. We need a connection between purchase_table and customer_table to know that, this is easy done with a join on customerID.

Problem 2 is that customerID_3 in account_table contains both customerID and customerID_2 in the same column. Its easy to find out all accountID that is using customerID as customerID_3 and from that find out all purchaseID. But we also want to find out which accountID that is connected to a customerID_2 and get their purchaseID.

Puh, its not simple to explain this

[montubhardwaj]

For the 1st two tables, i ll approach this way i.e. rather than doing a QV JOIN,I will join explicitly so that both Purchase and Customer tablesare in 1 QV table.

By using below method, I think you should be able to find out which accountID that is connected to a customerID_2 and get their purchaseID.

Hope I am making some sense

Regards,

Sharma