Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to join tables with 0 count rows.

Posted on 2012-04-02
7
Medium Priority
?
300 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 1000 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 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

572 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