asked on

Oracle Query Help

I have a query below...each table has the common element of Document_id, however the LINE_ITEMS_TABLE can have multiple rows in it. As such my results set is coming back larger than expected. How can have the query just pull one row from the LINE_ITEMS_TABLE?

SELECT <LIST Of VALUES>
FROM WORKFLOW_TABLE A,
HEADER_TABLE B,
LINE_ITEMS_TABLE C
WHERE A.QUEUE = 1
AND A.USER = 'MSMITH'
AND B.ORGID = '123'
AND A.DOCUMENT_ID = B.DOCUMENT_ID
AND C.COMPANY = '01'
AND C.DOCUMENT_ID = A.DOCUMENT_ID

ASKER CERTIFIED SOLUTION

David VanZandt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmpatton

ASKER

I tried that and it only returned 1 row over all. What I currently have in the data base is a row in the header table, a row in the workflow table, and then two rows in the line_items_table.

When I join them all up like below, it returns two records because there are two records in the line items table. In a live setting there will be hundreds of header records each with a corresponding workflow record and multiple line items for each.

SELECT A.INVOICE_NUMBER, B.WORKFLOW_QUEUE, C.COMPANY FROM WORKFLOW_TABLE A,
HEADER_TABLE B,
LINE_ITEMS_TABLE C
WHERE A.QUEUE = 1
AND A.USER = 'MSMITH'
AND B.ORGID = '123'
AND A.DOCUMENT_ID = B.DOCUMENT_ID
AND C.COMPANY = '01'
AND C.DOCUMENT_ID = A.DOCUMENT_ID

jmpatton

ASKER

I think I figured it out. The document id in the header table was duplicated.

Thanks for your help

David VanZandt

Best kind of solution, where there is effort and learning. You're welcome.