Solved

How to join tables with 0 count rows.

Posted on 2012-04-02
7
269 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
Comment Utility
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
Comment Utility
Error in mstchvs.*
0
 
LVL 15

Accepted Solution

by:
gplana earned 250 total points
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

Expert Comment

by:gplana
Comment Utility
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
Comment Utility
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
Comment Utility
ok
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now