Complex join?

[kolanupaka]

All,

I am novice in SQL/MySQL. I need a query to join two tables say table1 and table2. But the join should happen not based on PK, but based on other columns, which are non-unique. One of the columns joined in table2 is sum of all the numbers in a column, when the clause is satisfied. For example here are the tables:
table1:
tab1id, tab1no, tab1name,tab1va l
1,'01','one',10 0
2,'01','two',20 0
10,'02','ten',3 00
11,'02','eleven ',400
table2:
tab2id,tab2no,t ab2name
3,'01','three'
12,'02','twelve '

Now, I need a resulting table (after join) as this:
3,'01','three', 300
12,'02','twelve ',700

the fourth column is SUM(tab1val), when table1.tab1no = table2.tab2no.
Thank you,

Giri

[r035198x]

All,

I am novice in SQL/MySQL. I need a query to join two tables say table1 and table2. But the join should happen not based on PK, but based on other columns, which are non-unique. One of the columns joined in table2 is sum of all the numbers in a column, when the clause is satisfied. For example here are the tables:
table1:
tab1id, tab1no, tab1name,tab1va l
1,'01','one',10 0
2,'01','two',20 0
10,'02','ten',3 00
11,'02','eleven ',400
table2:
tab2id,tab2no,t ab2name
3,'01','three'
12,'02','twelve '

Now, I need a resulting table (after join) as this:
3,'01','three', 300
12,'02','twelve ',700

the fourth column is SUM(tab1val), when table1.tab1no = table2.tab2no.
Thank you,

Giri

What have you tried so far?

[coolsti]

All,

I am novice in SQL/MySQL. I need a query to join two tables say table1 and table2. But the join should happen not based on PK, but based on other columns, which are non-unique. One of the columns joined in table2 is sum of all the numbers in a column, when the clause is satisfied. For example here are the tables:
table1:
tab1id, tab1no, tab1name,tab1va l
1,'01','one',10 0
2,'01','two',20 0
10,'02','ten',3 00
11,'02','eleven ',400
table2:
tab2id,tab2no,t ab2name
3,'01','three'
12,'02','twelve '

Now, I need a resulting table (after join) as this:
3,'01','three', 300
12,'02','twelve ',700

the fourth column is SUM(tab1val), when table1.tab1no = table2.tab2no.
Thank you,

Giri

Is tab2no in table2 unique?

if so you could try this:

[code=sql]
select table2.tab2id,t able2.tab2name, table1.tab1no, sum(table1.tab1 val) as sumtab1val
from table1 inner join table2 on table1.tab1no=t able2.tab2no
group by table1.tab1no
[/code]

Unless I have missed the meaning of the problem and my solution is totally off, this is just a very basic join using an aggregate function (sum) and a group by. Something that you ought to be quite familiar with if you are going to work with MySql and SQL.

[kolanupaka]

Is tab2no in table2 unique?

if so you could try this:

[code=sql]
select table2.tab2id,t able2.tab2name, table1.tab1no, sum(table1.tab1 val) as sumtab1val
from table1 inner join table2 on table1.tab1no=t able2.tab2no
group by table1.tab1no
[/code]

Unless I have missed the meaning of the problem and my solution is totally off, this is just a very basic join using an aggregate function (sum) and a group by. Something that you ought to be quite familiar with if you are going to work with MySql and SQL.

Thanks Coolsti,

This is something similar that I was looking for, but unfortunately neither tab2no nor tab1no are unique in my case. But later I realized that this need not be done my join at all, this can be done by simply using two tables in inserting itself and using SUM with group by table1.tab1no. Thanks for the help.

Regards,

Kolanupaka