Have Geek Brain

How do I get prices for each invoice?

I am developing a back-end that processes about 500 invoices per day. I have the following tables in my database.

invoices
invoice_id int primary_key auto_increment
user_id varchar(10)
invoice_type enum { package, document }


users
user_id int primary_key auto_increment
rate_package_id int
rate_document_id int

rates
rate_id  int
rate_name  varchar(10)

rate_prices
price_id  int primary_key auto_increment
rate_id int
weight int
price double(8,2)

at the end of the day, I have to set a price for each invoice.

how do i get prices for each invoice? I actually have no ieda other than adding "rate_package_id" and "rate_package_document_id" to the invoice table so that I can directly query prices from rate_prices.

+1


a source to share


2 answers 


SELECT  invoice_id, price
FROM    invoices i
JOIN    users u
ON      u.user_id = i.user_id
JOIN    rates r
ON      r.rate_id = CASE WHEN i.invoice_type = package THEN u.rate_package_id ELSE u.rate_document_id END
JOIN    rate_prices p
ON      p.rate_id = r.rate_id

Not sure where you store the weight of your packages.



It seems like it should be invoices

, in which case just add it to the last condition JOIN

.

+3


a source


I guess you probably want the LineItems table to be associated with the invoice like:

lineitem_id int primary_key auto_increment
invoice_id int
price_id int
quantity int
total_price double(8,2)


you have a table to map prices to invoices, each invoice can have 1 or more items.

+1


a source






More articles:


All Articles