How can I fuse this into one code?

Question

2.33/5 (2 votes)

See more:

SQL-Server-2008R2

SQL

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblCedula INNER JOIN
             dbo.tblRequests ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

and this:

SQL

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblBarangayClearance INNER JOIN
             dbo.tblRequests ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

in the first code i have this result:
RecId CertType Quantity
1 Cedula 4
2 Clearance 0

and in the second code i have this result:
RecId CertType Quantity
1 Cedula 0
2 Clearance 2

Now i want to fuse it so i can have a result something like this:
RecId CertType Quantity
1 Cedula 4
2 Clearance 2

Note:tblBarangayClearance and tblCedula is not related to each other. But they are related to tblRequests and tblBasicPermitFeeSchedule if it's not obvious in the code.
Thanks in advance.

Posted 27-Aug-15 17:19pm

RodzGold

Updated 27-Aug-15 17:25pm

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 28-Aug-15 0:25am

I have no idea what you mean by "fusion", but keep in mind that high temperature can destroy data.
—SA

PIEBALDconsult 28-Aug-15 0:52am

UNION ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jgakenhe · Answer 1 · 2015-08-27T19:33:00

/*This should get you close*/
--one way is to use union all to get all rows then you perform the GROUPING once on the entire set. 
SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblCedula INNER JOIN
             dbo.tblRequests ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 
UNION ALL

SELECT     dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
COUNT(dbo.tblRequests.RequestNo)  AS   Quantity
FROM         dbo.tblBarangayClearance INNER JOIN
             dbo.tblRequests ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
             RIGHT OUTER JOIN
             dbo.tblBasicPermitFeeSchedule ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
 GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType

 --another way is to use a subquery
SELECT dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
	COUNT(dbo.tblRequests.RequestNo) AS QuantityRequests.
	(
		SELECT dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType,
		COUNT(dbo.tblRequests.RequestNo) AS Quantity
		FROM dbo.tblBarangayClearance 
		INNER JOIN dbo.tblRequests 
			ON dbo.tblBarangayClearance.RequestNo = dbo.tblRequests.RequestNo
		RIGHT OUTER JOIN dbo.tblBasicPermitFeeSchedule
			ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId
		GROUP BY dbo.tblBasicPermitFeeSchedule.RecId, dbo.tblBasicPermitFeeSchedule.CertType
	) AS QuantityRequestNo
FROM dbo.tblCedula 
INNER JOIN dbo.tblRequests 
	ON dbo.tblCedula.RequestNo = dbo.tblRequests.RequestNo
RIGHT OUTER JOIN dbo.tblBasicPermitFeeSchedule 
	ON dbo.tblRequests.CertType = dbo.tblBasicPermitFeeSchedule.RecId