CODEWITHSUNDEEP
oracle-database
Eric
Eric

Reputation: 3

Oracle duplicate field but still correct

So i built a query for my leadership team that was correct, but i dont understand why oracle gave me the correct answer.

i have 3 tables that i needed to get data out of in order to get the total billed amount.

Here is my query (please forgive me, my 2nd post and im not sure how to properly format my querys)

select b.total_amount_billed as billed from t1.billing_information b 
where b.billing_no in 
(select h.billing_no 
 from t1.res_history h where h.res_seq_no in 
(Select r.reservation_seq_no 
 from t1.res r where r.customer_order_no in ('THO40000') ))

so in the deepest select, i take the the sequence number where my customer order number was THO40000, this query returns 2 sequence numbers.

the second sub query returns the billing numbers for my order from the history table where the sequence number match, in this case for this order they both use the same billing number, 312000.

the final select, returns my total billed amount where it matched my billing numbers it found, in my case $110.

the query works, but what i dont understand is why is it not duplicated? why does it not return 110, for each time it found 312000, giving me 2 records of 110? the billing number is a PK in the billing_information table. im not sure why it worked without me using the distinct keyword on the query for the billing number.

anyway thanks for the help, ill do my best to explain if you have questions!

Upvotes: 0

Views: 59

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

You are being saved because you used IN to get the billing_no values to use, rather than an INNER JOIN between the two tables using b.billing_no = h.billing_no. A join would have duplicated the records, but your IN query is essentially this:

select b.total_amount_billed as billed
from t1.billing_information b 
where b.billing_no in (312000, 312000);

If there is a single row in billing_information having billing_no equal to 312000, it is in the list, so the WHERE condition is true and it is included in the results. The fact that it is in the list twice doesn't make the IN condition "more true".

Upvotes: 1

Related Questions