Query Problem

[gazal]

Hello Friends,

I have a very common requirement but a very difficult problem (at least for me)...

In my application i have a form for generating Requests for Quotations (RFQ's). In the detail part the user will select the Items.

I want to filter ONLY THOSE VENDORS who are supplying ALL THE ITEMS entered by the users.

I am having Item to Vendor Mapping table whose struncture is like this:

IV_MAP_ID NUMBER(10) PK,
VEND_ID NUMBER(10) FK,
ITEM_ID NUMBER(10) FK

AND THE DATE IS LIKE THIS:

IV_MAP_ID VEND_ID ITEM_ID
=======================================
1 V1 ITEM1
2 V1 ITEM2
3 V2 ITEM1
4 V3 ITEM1
5 V3 ITEM2
6 V4 ITEM5
7 V4 ITEM1


now lets say if the user selects
ITEM1 AND ITEM2 in the request form,

then i want to filter out only those vendors who are supplying both the items, so in above case it should be
V1 AND V3....

how can i achieve this...

please help me as its very urgent...

Thanks in advance,

Gazal

 

[Thargy]

Gazal,

run the following, and see for yourself.

CREATE TABLE gazal
    (
	IV_MAP_ID INTEGER,
	VEND_ID   VARCHAR2(2),
	MAP_ID    VARCHAR2(6)
	);

ALTER TABLE GAZAL ADD CONSTRAINT
UC_VENDOR_AND_ITEM
UNIQUE(VEND_ID, MAP_ID);

INSERT INTO GAZAL VALUES (1,'V1','ITEM1');
INSERT INTO GAZAL VALUES (1,'V1','ITEM2');
INSERT INTO GAZAL VALUES (1,'V2','ITEM1');
INSERT INTO GAZAL VALUES (1,'V3','ITEM1');
INSERT INTO GAZAL VALUES (1,'V3','ITEM2');
INSERT INTO GAZAL VALUES (1,'V4','ITEM5');
INSERT INTO GAZAL VALUES (1,'V4','ITEM1');
COMMIT;

SELECT VEND_ID FROM
    (
	SELECT VEND_ID, COUNT(*) NUM_OF_MATCHES
	  FROM GAZAL
	 WHERE MAP_ID IN ('ITEM1','ITEM2')
	 GROUP BY VEND_ID
	)
WHERE NUM_OF_MATCHES = 2;

Note that it is vital to add a unique constraint on VEND_ID and MAP_ID, otherwise you could get duplicate records in the table, and that would make your queries return false results.

The final select query is 'clunky' and I believe could be significantly improved upon (probably by using an analytic function). However, it does work, and your post said this is urgent.

Regards

Tharg

Grinding away at things Oracular

 

[gazal]

Thanks Tharg,

Thanks buddy that works really great...

Thank u...

Gazal