Home » SQL & PL/SQL » SQL & PL/SQL » how to write this query
how to write this query [message #381870] Tue, 20 January 2009 02:57 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Relation
EMPLOYEE-EMPLOYEE_ITEMS(1-M)(ID-EMP_ID)
INVOICE_ITEMS-EMPLOYEE_LOCATIONS(1-M)(ID-REF_ID_1)
INVOICE-INVOICE_ITEMS(1-M)(ID-INV_ID)

My requirement is the sum of the Qty of the respective invoice items should be equal to that employee qty

take 1st employee qty =250 which is there in invoice_items with qty 250 which are equal.
take 2nd employee qty=220 which is divided into two items(INVOICE_ITEMS) as 110,90 which is not equal.these 2 items should not come when i run below query.

SELECT *
FROM INVOICE_ITEMS IT, INVOICE IN,EMPLOYEE_LOCATIONS LOC,EMPLOYEE EMP
WHERE IN.ID =IT.INV_ID AND IT.ID=LOC.REF_ID_1 AND LOC.EMP-ID=EMP.ID
AND IN.ID=2233
When I run this query it should display only 12214-invoice_item id

EMPLOYEE		
		
ID	EMP_QTY	
5678	250	
5679	220	
		
EMPLOYEE_LOCATIONS		
		
ID	EMP_ID	REF_ID_1
12771	5678	12214
12772	5679	12215
12773	5679	12216
		
INVOICE_ITEMS		
		
ID	INV_ID	QTY
12214	2233	250
12215	2233	110
12216	2233	90
		
INVOICE		
ID	INVOICE NUMBER	
2233	INV1
 


Re: how to write this query [message #381872 is a reply to message #381870] Tue, 20 January 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you already tried.
Indent the code (See SQL Formatter), use code tags.

Regards
Michel

Re: how to write this query [message #381878 is a reply to message #381872] Tue, 20 January 2009 03:31 Go to previous message
chaituu
Messages: 115
Registered: June 2008
Senior Member
check this.this is the result i want

		SELECT INV.ID,EMP.ID,IIA.REF_ID_1,IT.ID
  FROM INVOICE INV, EMPLOYEE EMP, EMPLOYEE_LOCATIONS IIA ,INVOICE_ITEMS IT 
 WHERE 
 EMP.ID = IIA.EMP_ID
AND IT.INV_ID = INV.ID
 AND IIA.REF_ID_1 = IT.ID
   AND INV.id = 2233
   AND EMP.EMP_QTY = (SELECT SUM(IT1.QTY)
                           FROM INVOICE_ITEMS IT1 ,EMPLOYEE_LOCATIONS IIA1  
                          WHERE IT1.INV_ID = INV.ID
                                AND IT1.ID=IIA1.REF_ID_1
                                AND IIA1.EMP_ID=EMP.ID
                          );

Previous Topic: drop/create table in procedure
Next Topic: arabic date to english date
Goto Forum:
  


Current Time: Thu Dec 08 00:31:45 CST 2016

Total time taken to generate the page: 0.17029 seconds