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

Posted by Thiru under Others on 4/20/2012 | Points: 10 | Views : 2001 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

Latest Posts