Oracle Query from multiple tables

Question

Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places.

ZJLB_ORDERITEMS contains ORDER#, ISBN

ZJLB_BOOKS contains ISNB, TITLE, RETAIL, COST

SELECT ISBN
FROM ZJLB_ORDERITEMS
WHERE ORDER# = '1002';

(then I get an output of 8843172113)

SELECT TITLE,
       TO_CHAR(RETAIL - COST, '$99,999.99') "Profit"
FROM ZJLB_BOOKS,
WHERE ISBN = '8843172113';

(then I get the Title I want)

but I need to combine two scripts together because I can't manually find the ISBN first, then input WHERE ISBN = '8843172113';

Can I first output the value of ISBN found in script to X then in the script 2, Where ISBN= X (previous result).

Iswanto San · Accepted Answer · 2013-03-24 07:40:29Z

2

You can use JOIN clause:

An SQL join clause combines records from two or more tables in a database

Try this:

SELECT A.ISBN, B.TITLE, TO_CHAR(B.RETAIL - B.COST, '$99,999.99') "Profit"

FROM ZJLB_ORDERITEMS A JOIN ZJLB_BOOKS B

ON A.ISBN = B.ISBN

WHERE A.ORDER# = '1002';

See here for the join concept.

answered Mar 24, 2013 at 7:40

Iswanto San

18.6k13 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Query from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related