Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #231186] Sun, 15 April 2007 19:28 Go to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,

I have the following table schema,
SQL> desc groupdetails;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUPID NOT NULL NUMBER(5)
GROUPNAME VARCHAR2(100)
PRIORITY NOT NULL NUMBER(3)

SQL> desc apndetails;
Name Null? Type
----------------------------------------- -------- ----------------------------
APNID NOT NULL NUMBER(5)
APNNAME VARCHAR2(100)

SQL> desc groupapns;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUPAPNSID NOT NULL NUMBER(5)
GROUPID NOT NULL NUMBER(5)
APNID NOT NULL NUMBER(5)

I have a data such a way that , groupapns can contain the combination of groupid and apnid.

Following is the data set/:
SQL> select groupid, groupname from groupdetails;

GROUPID GROUPNAME
---------- -----------------------------------------
272 Undefined
236 WAP + MMS + Internet
238 Internet + MMS
237 WAP + Internet
239 WAP + MMS
240 Internet
241 WAP
242 Prepaid WAP + MMS + Internet
243 MMS
244 Business WAP + MMS + Internet
245 Business Internet + WAP

GROUPID GROUPNAME
---------- -----------------------------------------
246 Business Internet + MMS
247 Business WAP + MMS
248 Business Internet
249 Business WAP
250 PrePaid ALL
251 [2]WAP + MMS + Internet
252 [2]WAP + Internet
253 [2]Internet + MMS
254 [2]WAP + MMS
255 [2]Internet
256 [2]WAP

GROUPID GROUPNAME
---------- -----------------------------------------
257 [2]Prepaid WAP + MMS + Internet
258 [2]MMS
259 [2]Business WAP + MMS + Internet
260 [2]Business Internet + WAP
261 [2]Business Internet + MMS
262 [2]Business WAP + MMS
263 [2]Business Internet
264 [2]Business WAP
265 [2]PrePaid ALL
266 Service Provider
267 YESWAP

GROUPID GROUPNAME
---------- -----------------------------------------
268 TESTWAP
269 TESTMMS
270 Video Streaming
271 [2]Service Provider
SQL> select apnid, apnname from apndetails;

APNID APNNAME
---------- ----------------------------------
514 INTERNET
520 *
521 mms
522 trial3cmg
523 wap.optus.net.au
524 prepaidyesoptus
525 busintprv
526 buswap
527 FAST
528 fast
529 dummy

APNID APNNAME
---------- ----------------------------------
530 optuszoo
531 trail3cmg

SQL> select groupapnsid, groupid, a

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2546 251 528
2425 236 523
2426 237 514
2427 237 523
2428 238 514
2429 238 521
2430 239 523
2431 239 521
2432 240 514
2433 241 523
2434 242 514

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2435 242 521
2436 242 523
2437 242 524
2438 243 521
2439 244 521
2440 244 525
2441 244 526
2442 245 525
2443 245 526
2444 246 521
2445 246 525

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2446 247 521
2447 247 526
2448 248 525
2449 249 526
2423 236 514
2424 236 521
2450 250 514
2451 250 521
2452 250 524
2453 251 514
2454 251 521

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2455 251 523
2457 252 514
2458 252 523
2459 252 528
2460 253 514
2461 253 521
2462 253 528
2463 254 523
2464 254 521
2465 254 528
2466 255 514

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2467 255 528
2468 256 523
2469 256 528
2470 257 514
2471 257 521
2472 257 523
2473 257 524
2474 257 528
2475 258 521
2476 258 528
2477 259 521

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2478 259 525
2479 259 526
2480 259 528
2481 260 525
2482 260 526
2483 260 528
2484 261 521
2485 261 525
2486 261 528
2487 262 521
2488 262 526

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2489 262 528
2490 263 525
2491 263 528
2492 264 526
2493 264 528
2494 265 514
2495 265 521
2496 265 524
2497 265 528
2498 266 514
2499 266 521

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2500 266 523
2501 266 529
2502 267 530
2503 267 531
2504 268 530
2505 268 531
2506 269 521
2507 270 523
2508 271 514
2509 271 521
2510 271 523

GROUPAPNSID GROUPID APNID
----------- ---------- ----------
2511 271 529
2512 271 528


I need to retrieve a group which has a specific set of apnnames .

For example, by using the apnnames (Internet, wap.optus.net) I need to retreive the groupid (237).

since the groupapns table can contain the combinations of groupid and apnid, I need to retrieve the specific combination I am interested in.

I am using the following query to get the list of groupids. But while using the following query I get all the groupid which contain the APNnames and then I process the result set at the application to map the apns to a specific groupid. After processing the result set, I have a vector which has groupid as a key and the apnnames as the values. By checking the number of apns I can get the groupid.

Query being used:
SELECT DISTINCT GROUPDETAILS.GROUPID,GROUPDETAILS.GROUPNAME,
APNDETAILS.APNNAME
FROM
GROUPDETAILS,APNDETAILS,GROUPAPNS WHERE GROUPDETAILS.GROUPID=
GROUPAPNS.GROUPID AND GROUPAPNS.APNID=APNDETAILS.APNID AND
GROUPAPNS.GROUPID IN (SELECT DISTINCT GROUPDETAILS.GROUPID FROM
GROUPDETAILS, APNDETAILS, GROUPAPNS WHERE GROUPDETAILS.GROUPID=
GROUPAPNS.GROUPID AND GROUPAPNS.APNID= APNDETAILS.APNID AND
APNDETAILS.APNNAME IN ('INTERNET','wap.optus.net.au')AND
GROUPAPNS.GROUPID NOT IN (SELECT DISTINCT GROUPDETAILS.GROUPID FROM
GROUPDETAILS,APNDETAILS,GROUPAPNS WHERE GROUPDETAILS.GROUPID=
GROUPAPNS.GROUPID AND GROUPAPNS.APNID=APNDETAILS.APNID AND
APNDETAILS.APNNAME NOT IN ('INTERNET','wap.optus.net.au')))

Following is the orcle trace:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.04 0.03 0 5791 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.03 0 5791 0 14

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31

Rows Row Source Operation
------- ---------------------------------------------------
14 SORT UNIQUE
26 CONCATENATION
9 FILTER
39 NESTED LOOPS
39 NESTED LOOPS
39 NESTED LOOPS
13 NESTED LOOPS
13 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID APNDETAILS
1 INDEX UNIQUE SCAN UNIQUE_APNNAME (object id 27014)
13 TABLE ACCESS BY INDEX ROWID GROUPAPNS
13 INDEX RANGE SCAN GROUPAPNS_APNID_IDX (object id 27090)
13 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
39 INDEX RANGE SCAN GROUPAPN_IDX (object id 27021)
39 TABLE ACCESS BY INDEX ROWID APNDETAILS
39 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
39 TABLE ACCESS BY INDEX ROWID GROUPDETAILS
39 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
19 NESTED LOOPS
701 NESTED LOOPS
1663 TABLE ACCESS FULL GROUPAPNS
701 TABLE ACCESS BY INDEX ROWID APNDETAILS
1663 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
19 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
9 FILTER
63 NESTED LOOPS
63 NESTED LOOPS
63 NESTED LOOPS
21 NESTED LOOPS
21 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID APNDETAILS
1 INDEX UNIQUE SCAN UNIQUE_APNNAME (object id 27014)
21 TABLE ACCESS BY INDEX ROWID GROUPAPNS
21 INDEX RANGE SCAN GROUPAPNS_APNID_IDX (object id 27090)
21 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
63 INDEX RANGE SCAN GROUPAPN_IDX (object id 27021)
63 TABLE ACCESS BY INDEX ROWID APNDETAILS
63 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
63 TABLE ACCESS BY INDEX ROWID GROUPDETAILS
63 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
19 NESTED LOOPS
701 NESTED LOOPS
1663 TABLE ACCESS FULL GROUPAPNS
701 TABLE ACCESS BY INDEX ROWID APNDETAILS
1663 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
19 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
8 FILTER
45 NESTED LOOPS
45 NESTED LOOPS
45 NESTED LOOPS
14 NESTED LOOPS
14 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID APNDETAILS
1 INDEX UNIQUE SCAN UNIQUE_APNNAME (object id 27014)
14 TABLE ACCESS BY INDEX ROWID GROUPAPNS
14 INDEX RANGE SCAN GROUPAPNS_APNID_IDX (object id 27090)
14 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
45 INDEX RANGE SCAN GROUPAPN_IDX (object id 27021)
45 TABLE ACCESS BY INDEX ROWID APNDETAILS
45 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
45 TABLE ACCESS BY INDEX ROWID GROUPDETAILS
45 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010)
19 NESTED LOOPS
701 NESTED LOOPS
1663 TABLE ACCESS FULL GROUPAPNS
701 TABLE ACCESS BY INDEX ROWID APNDETAILS 2
1663 INDEX UNIQUE SCAN PK_APNDETAILS (object id 27013)
19 INDEX UNIQUE SCAN PK_GRPDETAILS (object id 27010) ********************************************************************************




But I would like to have a query which can return me the groupid for the specific apn set I am interested. Can some one help me in fine tuning the query?

Thanks,
Syam


Re: SQL Query [message #231187 is a reply to message #231186] Sun, 15 April 2007 19:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Too bad you chose to NOT follow the #1 Sticky post
http://www.orafaq.com/forum/t/42427/74940/
& did NOT use code tags to make your post much more readable.

[Updated on: Sun, 15 April 2007 19:48] by Moderator

Report message to a moderator

Re: SQL Query [message #231225 is a reply to message #231186] Mon, 16 April 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, instead of posting desc and select, post create table, indexes, constraints and insert statements.

Regards
Michel
Re: SQL Query [message #231397 is a reply to message #231186] Mon, 16 April 2007 22:58 Go to previous messageGo to next message
shyamh
Messages: 13
Registered: January 2007
Junior Member
Hi,
Please find the details regarding the indexes and constraints

Create table:

CREATE TABLE groupdetails (
groupid NUMBER(5) NOT NULL,
groupname VARCHAR2(100),
priority NUMBER(3) DEFAULT 1 NOT NULL,
CONSTRAINT PK_GRPDETAILS PRIMARY KEY (groupid),
CONSTRAINT UNIQUE_GROUPNAME UNIQUE (GROUPNAME)
);
CREATE TABLE groupapns (
groupapnsid NUMBER(5) NOT NULL,
groupid NUMBER(5) NOT NULL,
apnid NUMBER(5) NOT NULL,
CONSTRAINT PK_GRPAPNS PRIMARY KEY (groupapnsid),
CONSTRAINT FK_GRPID
   FOREIGN KEY (groupid)
   REFERENCES groupdetails(groupid) ON DELETE CASCADE,
CONSTRAINT FK_APNID
   FOREIGN KEY (apnid)
   REFERENCES apndetails(apnid) ON DELETE CASCADE
);



CREATE TABLE groupmsisdns (
groupmsisdnid NUMBER(10) NOT NULL,
groupid NUMBER(5),
msisdn VARCHAR2(23),
CONSTRAINT PK_GRPMSISDN PRIMARY KEY (groupmsisdnid),
CONSTRAINT FK_GRPID_GRPMSISDN
   FOREIGN KEY (groupid)
   REFERENCES groupdetails(groupid) ON DELETE CASCADE,
CONSTRAINT FK_MSISDN
   FOREIGN KEY (msisdn)
   REFERENCES SUBSCRIBERS(msisdn) ON DELETE CASCADE
);

CREATE TABLE apndetails (
apnid NUMBER(5) NOT NULL,
apnname VARCHAR2(100),
CONSTRAINT PK_APNDETAILS PRIMARY KEY (apnid),
CONSTRAINT UNIQUE_APNNAME UNIQUE (APNNAME)
);




TABLE_NAME	INDEX_NAME	ORDINAL_POSITION	COLUMN_NAME
APNDETAILS	(null)	0	(null)
APNDETAILS	PK_APNDETAILS	1	APNID
APNDETAILS	UNIQUE_APNNAME	1	APNNAME 



TABLE_NAME	INDEX_NAME	ORDINAL_POSITION	COLUMN_NAME
GROUPAPNS	(null)	0	(null)
GROUPAPNS	GROUPAPN_IDX	1	GROUPID
GROUPAPNS	GROUPAPN_IDX	2	APNID
GROUPAPNS	PK_GRPAPNS	1	GROUPAPNSID
GROUPAPNS	GROUPAPNS_APNID_IDX	1	APNID


TABLE_NAME	INDEX_NAME	ORDINAL_POSITION	COLUMN_NAME
GROUPDETAILS	(null)	0	(null)
GROUPDETAILS	PK_GRPDETAILS	1	GROUPID
GROUPDETAILS	UNIQUE_GROUPNAME	1	GROUPNAME


TABLE_NAME	INDEX_NAME	ORDINAL_POSITION	COLUMN_NAME
GROUPMSISDNS	(null)	0	(null)
GROUPMSISDNS	GROUPMSISDN_IDX	1	GROUPID
GROUPMSISDNS	GROUPMSISDN_IDX	2	MSISDN
GROUPMSISDNS	PK_GRPMSISDN	1	GROUPMSISDNID
GROUPMSISDNS	GROUPAPNS_GROUPID_IDX	1	GROUPID
GROUPMSISDNS	GROUPMSISDN_MSISDN_IDX	1	MSISDN



Regards,
Syam
Re: SQL Query [message #231547 is a reply to message #231397] Tue, 17 April 2007 08:02 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
These are not index creation scripts, nor are there any sample data creation scripts. Offer no help, get no help.
Previous Topic: Better PL/SQL
Next Topic: UTL_FILE format
Goto Forum:
  


Current Time: Wed Dec 07 13:00:36 CST 2016

Total time taken to generate the page: 0.09600 seconds