Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to join tables with 0 count rows.

Posted on 2012-04-02
7
Medium Priority
?
299 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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