Query not working if i change parameter in where condition [Resolved]

Posted by Thiru under Others on 4/20/2012 | Points: 10 | Views : 1011 | Status : [Member] | Replies : 5
Hi Friends,

I like to join 3 tables to get data as:
required fields from table1
and record_count from table2
and sum(amount) from table3

I am using the following query:

select a.cde,a.name,count(b.TransID) as t_cnt, sum(c.Amt) as c_sumofamt from table1 a inner join table2 b on a.cde=b.cde inner join
table3 c on a.cde=c.cde where a.Bcde='TVM' GROUP BY a.cde


Note: Its working fine if (where a.Bcde='TVM')
but not with anyother branch_code "Bcde"

TVM is having few records (just 40 records)

But if i give (where a.Bcde='CBE') its not working - taking long time and giving conn. err.
For your information its haivng more records:
6000+ records in table1
50000+ records in table2
100000+ records in table3

How to handle this situation?
Expecting your valuable reply.




Responses

Posted by: Gsm_Gsv on: 4/21/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
In the second innerjoin, use b.cde=c.cde

select a.cde,a.name,count(b.TransID) as t_cnt, sum(c.Amt) as c_sumofamt from table1 a inner join table2 b on a.cde=b.cde inner join

table3 c on b.cde=c.cde where a.Bcde='TVM' GROUP BY a.cde


---------------------------------------
Live the life you've dreamed

Regards
MADHU

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 4/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Madhu,
Thanks a lot - Its working fine.

Is it possible to explain the reason behind?
what is the difference of using b.cde=c.cde instead of a.cde=c.cde in second innerjoin.

Expecting your valuable reply.
Thanks & Regards,
Thiru.



Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gsm_Gsv on: 4/21/2012 [Member] Starter | Points: 25

Up
0
Down
The join is ambiguous since you are using condition of a.cde with two tables. Always perform JOINS in sequential way (like, a join b, b join c, c join d and so on...)

The WHERE clause filters the rows of your JOIN result and then display the final result. Also the WHERE syntax is more relational oriented.

I suggest you to check your query and my query without WHERE clause. There may be no difference, but it may cause errors for complex queries. So always perform joins in a sequential way...

---------------------------------------
Live the life you've dreamed

Regards
MADHU

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 4/21/2012 [Member] Starter | Points: 25

Up
0
Down
Oh Thanks Madhu.
Thanks for spending your valuable time here in this post.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/21/2012 [Member] Silver | Points: 25

Up
0
Down
Thanks Madhu

Regards,
Singaravel M

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response