MVSFORUMS.com

[showkath]

Hi,

Could you help me in writing a query for the below requirement
I need to find out the difference between two tables as follows

Table A:

Fld1, Fld2

Table B:

Fld1, Fld2

I want to count number of records in both the tables
for fld1=x and fld2=y (fox all x and y)
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain

[kolusu]

Showkath,

Try this

[vkphani]

SELECT COUNT(*) AS COUNT1 FROM TABLEA WHERE FLD1.TABLEA = 'X';

SELECT COUNT(*) AS COUNT2 FROM TABLEA WHERE FLD2.TABLEA = 'Y';

SELECT COUNT(*) AS COUNT3 FROM TABLEB WHERE FLD1.TABLEB = 'X';

SELECT COUNT(*) AS COUNT4 FROM TABLEB WHERE FLD2.TABLEB = 'Y';

[kolusu]

vkphani,

If you ever decide to answer a question make sure that you understand the question. Please do not give answers which will not serve any purpose except for confusing the poster. read showkath's question once again carefully. He needs to find the difference in counts between the 2 tables. Your queries just gives 4 counts which does not help showkath in any way.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[vkphani]

Kolusu,

COUNT1 - COUNT3 and COUNT2 - COUNT4 will give the desire result after finding the counts right.
I was suppose to mention this in my last post. By mistake I have submitted without these details.

[showkath]

Thanks guys for your excellent suppport.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain

[showkath]

Hi Kolusu,

Query suggested by you works fine. With that I have tried the below query
to display the number of records if the count does not match from both the
tables, for particular value of fld1 and fld2.


SELECT Y.LOT2,Y.CNT2,Z.LOT3,Z.CNT3
FROM

(SELECT A.fld1 AS LOT2,COUNT(*) AS CNT2
FROM table1 A
WHERE A.fld2 = 1 GROUP BY A.fld1 ) Y,

(SELECT B.LOT AS LOT3,COUNT(*) AS CNT3
FROM table2 B
WHERE B.fld2 = 1 GROUP BY B.fld1 ) Z

But it ends up in correlated sub-query.

i.e the count and fld1 is displaying correctly for y.lot2 and y.cnt2, but the first value of Z.lot3 and z.cnt3 is repeating.

Could you pls help me to write the appropriate query.

to summarize I want to display all the fld1 and the count wherever
teh corresponding recs does not match
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain

[Phantom]

showkath,

[showkath]

Phantom,

You are right. It should be B.FLD1 instead of B.LOT.


I want to select the four columns and compare them side by side for the corresponding Fld1. That's why i am not comparing the counts in the query.

If I run the two queries separately, I am getting the expected result. But I want to combine the query and see the difference.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain

[Phantom]

showkath,

You are missing one more thing in your query. Your query will produce A x B output since you did not use any join b/w the two tables.

For example assume that you inner query 1 returns 3 rows

[Phantom]

Nothing much... Just included the Where condition.

[showkath]

Phantom,

Thanks for that. It really solved my problem.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain