-
Hi,
There are two tables.
SQL> desc test
Name Null? Type
X NUMBER
Y NUMBER
SQL> desc test1
Name Null? Type
P NUMBER
SQL> select p a ,count(1) b FROM TEST1 GROUP BY P
2 /
A B
---------- ----------
1 3
2 2
SQL> select p1,COUNT(1) c FROM TEST2 GROUP BY P1
2 /
P1 C
---------- ----------
1 4
I have written a query (given below)
select a.a, a.b, b.c
FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
(select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b
A B C
---------- ---------- ----------
1 3 4
2 2 4
but for the second row (2 2 4 ) the c value should be null.
My requirement is for the matching records in both the queries the answer should come for C else it should return null.
Thanks in Advance
-
that is a simple join, just add your join condition (if any)
-
hi
SQL> select a.a, a.b, b.c
2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
3 (select p1 c1,COUNT(1) c FROM TEST2 GROUP BY P1) b
4 where a.a =b.c1
5 /
A B C
---------- ---------- ----------
1 3 4
1 select a.a, a.b, b.c
2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
3 (select p1 c1,COUNT(1) c FROM TEST2 GROUP BY P1) b
4* where a.a !=b.c1
5 /
A B C
--------- ---------- ----------
2 2 4
if you see my join for != condition it returns 4 but the answer should be null. how to avoid the 4 there (and thru a single query)
Thanks in advance.
-
-
1 select a.a, a.b, b.c
2 FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
3 (select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b
4* where a.a = b.c(+)
SQL> /
A B C
---------- ---------- ----------
1 3
2 2
When i use the outer join the query returns as above.
Regards
Shyla
-
i wonder why you compare a.a and b.c
-
Thanks a lot .
select a.a, a.b, b.c
FROM (select p a ,count(1) b FROM TEST1 GROUP BY P) a,
(select p1,COUNT(1) c FROM TEST2 GROUP BY P1) b
where a.a = b.p1(+)
/
A B C
----- ---------- ----------
1 3 4
2 2
The answer has come but if we use the select in a select will it decrease the performance ?
Regards
Shyla
