We help IT Professionals succeed at work.
Get Started

Oracle, multiple table query problems

vandy02
vandy02 asked
on
390 Views
Last Modified: 2012-08-29
I AM HAVING PROBLEMS TRYING TO RELATE THREE TABLES AND RETURN REQUIRED RESULTS:

TABLES WITH KEYCOLUMNS:

ORDER
---ORDER.ORDERNUM

ORDERLINES
---ORDERLINES.ORDERNUM
---ORDERLINES.WORKNUMBER

WORK
--WORK.WORKNUMBER
--WORK.WORKAVAILABLE


1 - ORDER HAS MANY ORDERLINES

2 - ORDERLINES CAN HAVE A WORKNUMBER BUT CAN ALSO HAVE NULL

3 - WORK IS ONLY ASSOCIATED TO INDIVIDUAL ORDERLINES - THE WORKNUMBER CAN SHOW UP IN MANY ORDERLINES AND VARYING ASSOCIATED ORDERLINE.ORDERNUMs

SETUP:
WOULD LIKE TO RETURN EACH ORDER AS IT LOOKS AT EACH ORDERLINE FOR WORKNUMBER AND MEETS CRITERIA BASED ON THE 3 SCENARIOS BELOW


THE FOLLOWING START QUERY RETURNS A RECORD FOR EACH ORDER AND SHOWS THE SUM OF ORDERLINES ALONG WITH A NULL IN ANOTHER COLUMN (WORKAVAILABLE).  THIS COLUMN SHOULD BE REFLECTIVE OF THE RELATIONSHIP DEFINED WITHIN THE 3 SCENARIOS BELOW WITHIN THE GOAL SECTION.  THIS IS GOOD BUT DOES NOT HAVE THE RELATIONSHIP TO WORK TABLE.

SELECT ORDER.ORDERNUM, SUM(ORDERLINES.COST) AS COST, NULL AS WORKAVAILABLE
FROM ORDER, ORDERLINES
WHERE ORDER.ORDERNUM = ORDERLINES.ORDERNUM
GROUP BY ORDER.ORDERNUM

GOAL:
I AM TRYING TO ALSO LOOK AT THE WORK TABLE BASED ON WORKNUMBER WITHIN THE ORDERLINES TABLE AND RETURN THE FOLLOWING RESULTS BASED ON THE FOLLOWING CRITERIA:

1 - IF THE ORDERLINES DO NOT HAVE ANY WORKNUMBERS I WOULD LIKE TO SHOW WORKAVAILABLE AS 0 SINCE NO WORK WAS FOUND

2 - IF THE ORDERLINES HAVE SOME WITH WORKNUMBERS AND ALL ASSOCIATED WORK.WORKAVAILABLE (WORK TABLE) SHOWS 0 I WOULD LIKE TO SHOW WORKAVAILABLE AS 0 SINCE WORK WAS FOUND BUT ALL SHOWED WORK.WORKAVAILABLE AS 0

2 - IF THE ORDERLINES HAVE SOME WITH WORKNUMBERS AND AT LEAST ONE ASSOCIATED WORK.WORKAVAILABLE (WORK TABLE) SHOWS 1 I WOULD LIKE TO SHOW WORKAVAILABLE AS 1 SINCE AT LEAST ONE REACORD SHOWS WORK.WORKAVAILABLE AS 1

Thanks
Comment
Watch Question
Project Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE