There are related tables заказы and рейсы . I want to calculate the cost of the order, based on the cost of all flights on this order. How to do it?

 CREATE TABLE `zakazy` ( `zakazy_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `summ` FLOAT ); CREATE TABLE `vzakaze` ( `vzakaze_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `zakazy_id` INT ); CREATE TABLE `vreise` ( `vreise_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `vzakaze_id` INT ); CREATE TABLE `reis` ( `reis_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `vreise` INT , `stoimost` FLOAT );
  • one
    Add Table Definition - Mirdin
  • one
    What a question and answer. Make a request that counts the cost of all flights, and then calculate the cost of the order ... - antonpp
  • Do I understand correctly that you are going to change zakazy.sum every time you change the reis table. This is, in principle, possible to do with the help of triggers, but this is a very bad idea. If you need such information, it is better to make a view. - Mirdin

2 answers 2

To select information from two tables, they must first be combined into one, this is done with the help of the JOIN FROM in the FROM section of the query. (It is not necessary that the tables be linked via FOREIGN KEY ).

Suppose we have tables:

 CREATE TABLE INVOICE ( Invoice_ID int identity(1,1) PRIMARY KEY ,Invoice_Description nvarchar(256) ); CREATE TABLE RUN ( Run_ID int identity(1,1) PRIMARY KEY ,Run_Descrition nvarchar(256) ,Run_Price decimal(18,2) ,Invoice_ID int FOREIGN KEY REFERENCES INVOICE(Invoice_ID) );

Then your request will look like this:

 SELECT i.Invoice_ID, SUM(r.Run_Price) as price FROM RUN as r inner join INVOICE as i ON r.Invoice_ID = i.Invoice_ID WHERE i.Invoice_ID = 3 -- замените 3 на ваш номер заказа GROUP BY i.Invoice_ID

UPD

zakazy a zakazy table every time every time you change a reis table is a very bad idea although, in principle, it can be done with the help of triggers. If you need such information, it is better to make a view. I would recommend to throw out the summ field from the zkazy table (give them normal English names, by the way) and make this view:

 CREATE VIEW vZakazy AS SELECT z.zakazy_id, SUM(r.stoimost) as stoimost FROM reis as r inner join vreise as v ON r.vreise = v.vreise_id inner join vzakaze as vz ON v.vzakaze_id = vz.vzakaze_id inner join zakazy as z ON vz.zakazy_id = z.zakazy_id GROUP BY z.zakazy_id GO

    Addition to the post Mirdin:

    Or it is possible to use window functions, if the DBMS allows it, then you can get rid of WHERE:

    Then your request will look like this:

     SELECT DISTICT i.Invoice_ID, SUM (r.Run_Price) OVER (PARTITION BY i.Invoice_ID) as price
 FROM RUN as r inner join INVOICE as i ON r.Invoice_ID = i.Invoice_ID
 GROUP BY i.Invoice_ID

    If you need an average value, then not SUM, but AVG, respectively.