"Zero Sales products"

Hi All
I Need Some Help In Writing A Query In SQL.

I Have The Below Tables

SQL> desc sales_invoice;
Name Null? Type
----------------------------------------------------------------------------------- -------- ------
INVOICE_NO NUMBER(3)
INVOICE_DATE DATE
PRODUCT_CODE VARCHAR2(2)
QUANTITY NUMBER(6,3)
RATE NUMBER(6,2)
VALUE NUMBER(10,2)
SALES_TAX_TYPE VARCHAR2(6)
SALES_TAX_AMOUNT NUMBER(10,2)
TOTAL_VALUE NUMBER(10,2)
PLACE VARCHAR2(2)

SQL> select * from sales_invoice;

INVOICE_NO INVOICE_D PR QUANTITY RATE VALUE SALES_ SALES_TAX_AMOUNT TOTAL_VALUE PL
---------- --------- -- ---------- ---------- ---------- ------ ---------------- ----------- --
1 02-JUL-04 01 3 15 45 APGST 3.6 48.6 01
2 02-JUL-04 03 1 23 23 CST 2.76 25.76 03
3 03-JUL-04 03 2 21 42 42 02
4 04-JUL-04 02 4 19 76 APGSTG 3.04 79.04 02
5 05-JUL-04 05 5 11 55 CSTC 2.2 57.2 04
6 02-AUG-04 04 1 65 65 APGST 5.2 70.2 01
7 07-AUG-04 03 3 14 42 42 03
5 10-AUG-04 05 -3 11 -33 CSTC -1.32 -34.32 04
8 11-AUG-04 02 4 12 48 CST 5.76 53.76 03
9 19-AUG-04 02 3 17 51 APGSTG 2.04 53.04 02
10 05-SEP-04 01 5 15 75 CST 9 84 03
10 06-SEP-04 01 -5 15 -75 CST -9 -84 03
11 07-SEP-04 03 3 25 75 CST 9 84 04
12 15-SEP-04 05 5 15 75 APGST 4 54 02
13 20-SEP-04 03 3 20 60 APGSTG 2.4 62.4 01

15 rows selected.

SQL> desc product;
Name Null? Type
----------------------------------------------------------------------------------- -------- ------
PRODUCT_CODE VARCHAR2(2)
PRODUCT_NAME VARCHAR2(20)
LIST_PRICE NUMBER(6,2)
COST NUMBER(6,2)
UNIT VARCHAR2(3)

SQL> select * from product;

PR PRODUCT_NAME LIST_PRICE COST UNI
-- -------------------- ---------- ---------- ---
01 TOOTH PASTE 15 14 NO
02 SOAP 20 15 NO
03 SHAMPOO 25 22 NO
04 SUNFLOWER OIL 65 45 KG
05 MINERAL WATER 12 10 LT

SQL> desc place;
Name Null? Type
----------------------------------------------------------------------------------- -------- ------
PLACE VARCHAR2(2)
PLACE_NAME VARCHAR2(20)

SQL> select * from place;

PL PLACE_NAME
-- --------------------
01 HYDERABAD
02 SECUNDERABAD
03 BANGLORE
04 CHENNAI

Here I Want To Find Out "Place wise,Productname wise-'zero sales products.
Thanks In Advance
Sasi

If I understand your question correctly then following query might be help you, but 100% satisfaction not guranteed, you can make certain changes to fullfil your requirement.

SELECT p.place_name, r.product_name, r.list_price, r.cost
FROM place p, product r
WHERE NOT EXISTS (SELECT 1 FROM sales_invoice s
WHERE .product_name = s.product_name
AND s.place = p.place)
/

If I understand your question correctly then following query might be help you, but 100% satisfaction not guranteed, you can make certain changes to fullfil your requirement.

SELECT p.place_name, r.product_name, r.list_price, r.cost
FROM place p, product r
WHERE NOT EXISTS (SELECT 1 FROM sales_invoice s
WHERE .product_name = s.product_name
AND s.place = p.place)
ORDER BY 1, 2
/

If I understand your question correctly then following query might be help you, but 100% satisfaction not guranteed, you can make certain changes to fullfil your requirement.

SELECT p.place_name, r.product_name, r.list_price, r.cost
FROM place p, product r
WHERE NOT EXISTS (SELECT 1 FROM sales_invoice s
WHERE .product_name = s.product_name
AND s.place = p.place)
ORDER BY 1, 2
/