Solved

How to write a join sql query for following conditions.

Posted on 2010-11-25
4
149 Views
Last Modified: 2012-08-14
1.Table 1
Txnenrollment contains familyid,enrolled,cardsprinted,personalized,pcode,blockcode.
2.Table 2
Txnissuance contains those familyid which will be there in txnenrollment
and will have status enrolled=1 and personalized=1 and cardsprinted=1
3.Table 3
mstpanchayat will contain pcode and pname
4.Table 4
mstblock will contain blockcode and blockname
I want to list blockname,pname and count of total enrolled,total personalized and total cardsprinted with a check where all familyid should exist in txnissuance.
0
Comment
Question by:searchsanjaysharma
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Andre412
Comment Utility

Can you give more information about the relationships between the tables
0
 
LVL 6

Accepted Solution

by:
rkannan2000 earned 500 total points
Comment Utility
This might help you....

SELECT D.BLOCK_NAME, C.PNAME, SUM(A.ENROLLED),SUM(A.PERSONALIZED),SUM(A.CARDHOLDER), COUNT(A.FAMILY_ID)-COUNT(B.FAMILY_ID)
FROM Txnenrollment A
LEFT OUTER JOIN Txnissuance B ON A.FAMILY_ID=B.FAMIL_ID
JOIN mstpanchayat C ON C.PCODE=A.PCODE
JOIN mstblock D ON D.BLOCKCODE=A.BLOCKCODE
GROUP BY D.BLOCK_NAME, C.PNAME
0
 

Author Comment

by:searchsanjaysharma
Comment Utility
pcode is primary key in mstpanchayat,
blockcode is primary key in mstblock
and pcode and blockcode exists in txnenrollment
and txnissuance will have only those familyid
which will exist in txnenrollment and will have status of enrolled=1 and personalized=1 and cardsprinted=1
0
 

Author Closing Comment

by:searchsanjaysharma
Comment Utility
ok
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

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 …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

13 Experts available now in Live!

Get 1:1 Help Now