CODEWITHSUNDEEP
sqloracle-database
Gab
Gab

Reputation: 1

Oracle Query from multiple tables

  1. 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).

Upvotes: 0

Views: 328

Answers (1)

Iswanto San
Iswanto San

Reputation: 18569

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.

Upvotes: 2

Related Questions