CODEWITHSUNDEEP
sql
basodre
basodre

Reputation: 5770

SQL Query: How to multiply values in Table A by Value in Table B?

I'm drawing a blank on the below issue.

   Table A             Table B
Item    Amount      Item    Multiplier 
Bread   100         Bread   1.7
Milk    100         Cheese  1.8
Cheese  100

I need to run a query that returns each Item in Table A as well as the corresponding Amount * Multiplier. The caveat is that any item without an entry in Table B should default to a multiplier of 1.5. The query results should look as follows:

   Query Results          
Item    Amount      
Bread   170         
Milk    150         
Cheese  180

Thanks for any help.

Upvotes: 1

Views: 96

Answers (1)

rs.
rs.

Reputation: 27427

Try this

SELECT A.ITEM, A.AMOUNT * COALESCE(B.Multiplier, 1.5) Amount
FROM TableA A
LEFT OUTER JOIN TableB B ON A.ITEM = B.ITEM

Upvotes: 6

Related Questions