join 3 tables to retrieve data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join 3 tables to retrieve data

Author

Topic

sandhyakalsotra
Starting Member

22 Posts

Posted - 2010-08-02 : 06:19:09

I have to retrieve data from 3 tables "mconsumer","billprocess" and "revcoll" for sum of units consumed, total no of consumers, totalload and total revenue received in a particular month. if i simply join these all tables, the rows retrieved are of only those consumers who have deposited their bills and rest are not included though they have units consumed etc. I again used left outer join for revcoll table but the result remains same. When i remove revcoll, there are 129 rows but after joining revcoll, only 34 rows are retrieved. how to join 3 tables.

the rows in table A & B are same, problem is with Table C that has lesser rows than both A&B. I am using this query
SELECT count(consumercodes) as consumertotal, sum ( unitsconsumed)as units, sum ( sanctioload) as load, sum ( amount) as revenue

FROM billprocess INNERJOIN mconsumer on billprocess.consumercode=mconsumer.consumercode
LEFT JOIN revcoll on revcoll.consumercode=billprocess.consumercode
where billyear=2010 and revcollyear=2010 and feedercode='DB01'

( the columns consumercode, unitsconsumed and sanctionload are in billprocess table, the column feedercode is in mconsumertable while amount is in revcoll table, all tables have a common column "consumercode'_)

sandhya

sandhya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-02 : 07:34:27

Maybe it belongs to your WHERE clause?
You havn't posted the table structure so we can't see in which table are the columns in that WHERE clause.

No, you're never too old to Yak'n'Roll if you're too young to die.