OraFAQ Forum: Reports & Discoverer » report join problem

Home » Developer & Programmer » Reports & Discoverer » report join problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

report join problem [message #132587]

Mon, 15 August 2005 00:16

rzkhan
Messages: 370
Registered: March 2005

Senior Member

Dear All
I am trying to design a report with following cols.

item_id, qty_issued, qty_returned qty_balance

If there are 3 records in items_issued table and 1 record in return_to_store table. the following query returns only one record. I wonder how can I retrieve all the three records with corresponding columns.

The basic questions is above. I am submitting the query also as below.

SELECT ALL ITEMS.ITEM_ID, ITEMS.ITEM_NAME, ITEMS.ITEM_DESC,
EMPLOYEES.NAME, DESIGNATION.DESIGNATION, ITEMS_ISSUED.QTY_ISSUED, ITEMS_ISSUED.REMARKS,
ITEMS_ISSUED.ISSUE_DATE, RETURN_TO_STORE.ITEM_ID, RETURN_TO_STORE.EMP_ID,
RETURN_TO_STORE.QTY_RET, RETURN_TO_STORE.DATE_RET
FROM ITEMS, ITEMS_ISSUED, EMPLOYEES, DESIGNATION, RETURN_TO_STORE
WHERE ((ITEMS_ISSUED.ITEM_ID = ITEMS.ITEM_ID)
AND (ITEMS_ISSUED.EMP_ID = EMPLOYEES.EMP_ID)
AND (EMPLOYEES.DESIGNATIONID = DESIGNATION.DESIGNATIONID)
AND (RETURN_TO_STORE.EMP_ID = EMPLOYEES.EMP_ID)
AND (RETURN_TO_STORE.ITEM_ID = ITEMS.ITEM_ID))

Report message to a moderator

Re: report join problem [message #132647 is a reply to message #132587]

Mon, 15 August 2005 08:52

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

It looks like you want "all items issued and the same items returned to store if any", right?
In that case, use outerjoins. And maybe you should check the other joins to, because from what I'm reading in you code, you only select the items that are indeed returned to store and (mandatory) handled by the same employee that sold the items. Are you sure that is what you mean?

Maybe you mean something like:

SELECT itm.item_id
      ,itm.item_name
      ,itm.item_desc
      ,emp1.NAME employee_issued_item
      ,emp2.NAME employee_received_returned_item
      ,des.designation designation_of_employee_issued_item
      ,itis.qty_issued
      ,itis.remarks
      ,itis.issue_date
      ,ret.item_id
      ,ret.emp_id /* why include the emp_id here, if you have the name? */
      ,ret.qty_ret
      ,ret.date_ret
FROM   items itm
JOIN   items_issued itis ON itis.item_id = itm.item_id
JOIN   employees emp1 ON itis.emp_id = emp1.emp_id
JOIN   designation des ON emp1.designationid = des.designationid
LEFT   OUTER JOIN return_to_store ret ON ret.item_id = itm.item_id
LEFT   OUTER JOIN employees amp2 ON ret.emp_id = emp2.emp_id

Regards,
Sabine

Report message to a moderator

Previous Topic:	rwTemplates.xml
Next Topic:	order by month

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:53:49 CST 2024