| Subquery to retreive irregular result set [message #9795] |
Thu, 04 December 2003 23:30  |
Iyappan
Messages: 12
Registered: December 2003
|
Junior Member |
|
|
hi friends,
I am a novice to PL/SQL. i have got landed up into a problem.
Consider the following tables
1. CUSTOMER
Cust_ID
CUST_NAME
CUST_GROUP_NO .....
2. CONT_DL_INFO
CUST_ID
CONT_NO
3. CUST_GROUP
CUST_GROUP_NO
CUST_GROUP_NAME
Relationship of the above tables are
CUST::Cust_ID <=> CONT_DL_INFO::Cust_ID and
CUST::CUST_GROUP_NO <=> CUST_GROUP::CUST_GROUP_NO
i want to fetch details from cust table and the cust_group_name from cust_group and count(cust_id) from cont_dl_info. is there a way to do it in a single query? please give me suggestions
all are welcome
thanks
iyappan
|
|
|
|
| Re: Subquery to retreive irregular result set [message #9810 is a reply to message #9795] |
Fri, 05 December 2003 04:12  |
ramana
Messages: 51
Registered: December 2000
|
Member |
|
|
plz check out
SQL> select * from customer;
CUST_ID CUST_NAME CUST_GROUP_NO
---------- ------------------------------ -------------
1000 ramana 1
1001 srinivas 1
1002 raju 2
SQL> select * from cont_dl_info;
CUST_ID CONT_NO
---------- ----------
1000 1
1000 2
1000 3
1001 1
1002 1
SQL> select * from cust_group;
CUST_GROUP_NO CUST_GROUP_NAME
------------- ------------------------------
1 c group name 1
2 c group name 2
SQL> select c.cust_id, c.cust_name, c.cust_group_no, g.cust_group_name, n.cnt
2 from
3 customer c,
4 cust_group g,
5 (select cust_id, count(*) cnt from cont_dl_info group by cust_id) n
6 where
7 c.cust_id = n.cust_id and
8 c.cust_group_no = g.cust_group_no;
CUST_ID CUST_NAME CUST_GROUP_NO CUST_GROUP_NAME CNT
---------- ------------------------------ ------------- ------------------------------ ----------
1000 ramana 1 c group name 1 3
1001 srinivas 1 c group name 1 1
1002 raju 2 c group name 2 1
SQL>
|
|
|
|
| Re: Subquery to retreive irregular result set [message #9833 is a reply to message #9810] |
Sun, 07 December 2003 17:44 |
Iyappan
Messages: 12
Registered: December 2003
|
Junior Member |
|
|
Hi ramana,
Thanks for your great effort. your query was useful.
And my problem is a still more complicated can you help me. Now i want to select all the customer details from the customer table and their corresponding group name and the count of for each customer in the cont_dl_info. like the following
Cust_ID Cust_Name Group_name DL_Count
~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~
1 iyappan Administrator 90
2 Ramana Super User 100
3 ellison guest 20
4 page guest 0
5 kikuci power user 0
i want the query to produce the result as above. i.e., i want the customers signed up between date1 to date2 consider the customer table consists the signup_date field. There may or may not be records for a given Customer in the CONT_DL_INFO so for customer who does not having a entry in the download info should get a count 0 and who has the entry should get the count of the entries in CONT_DL_INFO.
thanks
iyappan
|
|
|
|
| Re: Subquery to retreive irregular result set [message #9845 is a reply to message #9833] |
Mon, 08 December 2003 04:09 |
ramana
Messages: 51
Registered: December 2000
|
Member |
|
|
just use outer join feature & nvl function as shown below.
SQL> select c.cust_id, c.cust_name, c.cust_group_no, g.cust_group_name, NVL(n.cnt,0)
from
customer c,
cust_group g,
(select cust_id, count(*) cnt from cont_dl_info group by cust_id) n
where
c.cust_id = n.cust_id(+) and
c.cust_group_no = g.cust_group_no;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
