Solved

How to join tables with 0 count rows.

Posted on 2012-04-02
7
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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