how to write this query [message #381870] |
Tue, 20 January 2009 02:57  |
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 #381878 is a reply to message #381872] |
Tue, 20 January 2009 03:31  |
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
);
|
|
|