Solved

How to join tables with 0 count rows.

Posted on 2012-04-02
7
275 Views
Last Modified: 2012-04-02
I have two tables.
1.geotable - It contains source,state and district.
There are total 48 districts. This is master table.
2.mstchvs - Here data entry is done and source,state district fields are there for every records.

I want the following summary
State,District,source,Total_count as 'No. of enteries'
The query should display 0 for those records which doesnot exists for a given condition of dates.

select geotable.state,geotable.district,geotable.source,count(*)
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
where flagr=1 and rfeeddate>='2012-1-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1 asc,2 asc ,3 asc

This query return only those rows where record exists.
0
Comment
Question by:searchsanjaysharma
  • 4
  • 2
7 Comments
 
LVL 39

Assisted Solution

by:appari
appari earned 250 total points
ID: 37794927
try this

select geotable.state,geotable.district,geotable.source,count(mstchvs.*)
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-1-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1 asc,2 asc ,3 asc
0
 

Author Comment

by:searchsanjaysharma
ID: 37794957
Error in mstchvs.*
0
 
LVL 15

Accepted Solution

by:
gplana earned 250 total points
ID: 37794991
You should use LEFT JOIN when you want all records on the left table. In this case, all values on the right table will be set to null on the result of the query.

Try this:

SELECT g.state, g. g.district, g.source, count(*)
FROM geotable g
LEFT JOIN mstchvs m
ON g.state=m.state AND g.district=m.district AND g.source=m.source
AND m.flagr =1 AND m.rfeeddate BETWEEN '2012-1-1' AND '2012-3-31'
GROUP BY g.state, g.district, g.source
ORDER BY g.state, g. g.district, g.source

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:searchsanjaysharma
ID: 37795002
Sorry, geotable contains only state,district and source.
0
 
LVL 15

Expert Comment

by:gplana
ID: 37795008
I have edited my query. Try now.

I think actually there is a small error: instead of count(*) you should put count(m.district) or any field on table mstchvs that is NOT NULL
0
 

Author Comment

by:searchsanjaysharma
ID: 37795144
I am getting the out without this query, now how to get the cumulative output also
State District Source Count Cum.Count


select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0))
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
order by 1,2,3
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37795271
ok
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question