count of rows from different database

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hello everyone..

I have two database A & B in both the database there is a table called "planner"..
table structure is as follow:

Table A :

Id    Topicid         Resourceid              Isdeleted
1       596             3                          false
2       359             5                          false
3       596             8                          true
3       596             10                         true
3       596             12                         true

Table B:

Id    Topicid         Resourceid              Isdeleted
1       596             3                          false
2       359             5                          false
3       596             8                          true
3       596             10                         true
3       596             12                         false

I need a query for the isdeleted="true" which gives me a result like this


Topicid         isDeletedCountA           isDeletedCountB
359                0                          0
596                3                          2


I have tried this thing , but its not working perfectly..

Query :


select A.TopicId,Count(A.isdeleted),count(B.isdeleted) from
A inner join B on A.TopicId=B.TopicId
where A.isdeleted='true'
and B.isdeleted='true'
group by A.TopicId
order by A.topicid

Please help me out...

Regards,
Krunal

Posted 27-Jun-13 2:42am

krunalpanchalN

Add a Solution

3 solutions

Solution 2

Try this:

SQL

DECLARE @tableA TABLE (Id INT, Topicid INT, Resourceid INT, Isdeleted VARCHAR(10))

INSERT INTO @tableA (Id, Topicid, Resourceid, Isdeleted)
SELECT 1 AS Id, 596 AS Topicid, 3 AS Resourceid, 'false' AS Isdeleted
UNION ALL SELECT 2, 359, 5, 'false'
UNION ALL SELECT 3, 596, 8, 'true'
UNION ALL SELECT 3, 596, 10, 'true'
UNION ALL SELECT 3, 596, 12, 'true'

DECLARE @tableB TABLE (Id INT, Topicid INT, Resourceid INT, Isdeleted VARCHAR(10))

INSERT INTO @tableB (Id, Topicid, Resourceid, Isdeleted)
SELECT 1 AS Id, 596 AS Topicid, 3 AS Resourceid, 'false' AS Isdeleted
UNION ALL SELECT 2, 359, 5, 'false'
UNION ALL SELECT 3, 596, 8, 'true'
UNION ALL SELECT 3, 596, 10, 'true'
UNION ALL SELECT 3, 596, 12, 'false'

SELECT Topicid, Isdeleted, [A], [B]
FROM(
    SELECT Topicid, 'A' AS TableName, Isdeleted
    FROM @tableA
    UNION ALL
    SELECT Topicid, 'B' AS TableName, Isdeleted
    FROM @tableB
) AS DT
PIVOT(COUNT([Tablename]) FOR Tablename IN([A],[B]))AS PT
--WHERE Isdeleted = 'true'

Result:

Topicid Isdeleted	A	B
359	false		1	1
596	false		1	2
596	true		3	2

Posted 27-Jun-13 2:57am

Maciej Los

Updated 27-Jun-13 11:13am

v2

Comments

gvprabu 27-Jun-13 9:06am

Hi friend,
HRU? yes your Query is correct.... but he needs TableA and TableB Count in same ROW. You checked with any sample data

Maciej Los 27-Jun-13 16:48pm

OK, thank you for your information, Gopal. I forgot to mention that OP needs to use Pivot ;)

gvprabu 28-Jun-13 5:29am

great work yar... my 5+

Maciej Los 28-Jun-13 6:42am

Thank you, Gopal ;)

Solution 1

See, for instance, here: "How To Query Multiple Databases and Generate Reports"[^].

Posted 27-Jun-13 2:55am

CPallini

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)

gvprabu · Accepted Answer · 2013-06-27T03:05:00

Solution 3

Hi,

Check the below Script.....

SQL

SELECT T.Topicid,
	(SELECT ISNULL(COUNT(Resourceid),0) FROM TableA WHERE Topicid=T.Topicid AND Isdeleted=1) 'isDeletedCountA',
	(SELECT ISNULL(COUNT(Resourceid),0) FROM TableB WHERE Topicid=T.Topicid AND Isdeleted=1) 'isDeletedCountB'
FROM (SELECT Topicid FROM TableA
	  UNION 
      SELECT Topicid FROM TableB) T

Regards,
GVPrabu

Posted 27-Jun-13 3:05am

gvprabu

Comments

Maciej Los 27-Jun-13 17:15pm

Good work!
+5!
See my answer after update ;)

gvprabu 28-Jun-13 5:25am

Thanks.... friend