how to outer join - SQL Server Forums

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

how to outer join

Author

Topic

sandhyakalsotra
Starting Member

22 Posts

Posted - 2010-08-02 : 05:31:34

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.

sandhya

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 05:42:56

If you do


SELECT ...
FROM TableA AS A
     LEFT OUTER JOIN TableB AS B
         ON B.ID = A.ID
     LEFT OUTER JOIN TableC AS C
         ON C.ID = A.ID

(note that both TableB and TableC join to A.ID) then every matching row in TableB will join to every matching row in TableC (so called Cartesian Join).

So if RecordA has 2 rows in TableB and 3 rows in TableC the above query will give you 2 x 3 = 6 rows.


SELECT ... ,
       (
           SELECT COUNT(*) 
           FROM TableB AS B
           WHERE B.ID = A.ID
       ) AS TableB_Count,
       (
           SELECT COUNT(*) 
           FROM TableC AS C
           WHERE C.ID = A.ID
       ) AS TableC_Count
FROM TableA AS A
     LEFT OUTER JOIN TableB AS B
         ON B.ID = A.ID
     LEFT OUTER JOIN TableC AS C
         ON C.ID = A.ID

may be the sort of thing you are looking for?

sandhyakalsotra
Starting Member

22 Posts

Posted - 2010-08-02 : 06:00:30

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

sandhyakalsotra
Starting Member

22 Posts

Posted - 2010-08-02 : 06:06:49

@ Kristen, shall I have to outerjoin revcoll with both mconsumer and billprocess or only with billprocess?

sandhya

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-08-04 : 08:01:14

You have to use OUTER JOIN with both mconsumer and billprocess tables. The reason being the customers who haven't paid the bill will not be there in both the tables i.e. billprocess and revcoll.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-08-04 : 08:24:24

If too many left joins are confusing you then try the following:

SELECT COUNT(*),..... FROM mConsumer A
LEFT OUTER JOIN
(
SELECT consumerId,.......
FROM billprocess AS B
INNER JOIN revcoll C
ON B.consumerId= C.consumerId
)D
ON A.consumerId=D.consumerId

The Query
SELECT consumerId,.......
FROM billprocess AS B
INNER JOIN revcoll C
ON B.consumerId= C.consumerId

will give you the customers who have paid the bills and their respective amount,loads etc. The LEFT OUTER JOIN of this query with mConsumer table will give you the list of all the consumers in mConsumer table and their amount,load,etc from the other two tables. The consumers who haven't paid their bills will have NULL for these columns. I hope this is your requirement.

After reading your post once again i think the following query is more suitable:

SELECT .....
FROM mConsumer AS A
INNER JOIN billprocess AS B
ON A.consumerId=B.consumerId
LEFT OUTER JOIN revcoll AS C
ON A.consumerId=C.consumerId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources