insert values from other tables - sql

[greatcornholio]

Hi all,
this is maybe a big stupid question, but cant get this to work

thing is that i have 5 tables:
airlines - hotels - costs - projects and payments
being costs my main table.

In this costs table I need to have in the 'total' column the sum of different values that are in the other tables.

For example in the airlines table i have the name of the airline - type of seat (if tourist - executive - etc) and the value of this seat.
then in the hotels i have the name , type of room and it's value.
and the payment has the name and if it has interest (ex for a credit card).
the projects table exists only to assign the cost to it

so, i would need to sum the airlines cost + hotel cost and this multiplied by the interest if existed.

i think it is something like this:

INSERT INTO costos VALUES(1,20,1,1,100,100,1,1,sum(100+100+aerolineas.costo*20+alojamiento.valor_habitacion*20))

where:
1=project id
20=amount of people
1=airline id
1=hotel id
100=amount spent in car rental, etc
100=amount spent in other stuff
1=id of the way of payment (cash - credit card, etc)
1=amount of fees

aerolineas is the airlines table
alojamiento is the hotels table

thanks in advance!

ps: i attached a zip file with the data stored in all the other tables except for costs, that doesnt have any data

[adam_k]

How are you going to use this insert? Is it going to be part of a program that will insert a record or part of a procedure/query or whatever that will update all/some records from the costs table?
The reason for asking is that if this is a program then you might want to just hold the values in variables and be done with it.

If it's a procedure (or a query) then you've got to tell us if this is going to be a trigger/cursor or not. If it is then again it might be easier to go with vars.

If it is not then you are doing it wrong. You can't use tables with VALUES . Instead you have to use insert :

INSERT INTO costos 
select 1,20,1,1,100,100,1,1,(100+100+aerolineas.costo*20+alojamiento.valor_habitacion*20) from aerolineas inner join alojamiento on ...

I removed the sum() as you were using it like in Excel and it won't work like that.

Also you need to join the airlines and the hotels tables to get the values and I'm not sure how you are going to (perhaps by joining the project table as well?)

PS: If 20 is the number of people don't you need number of nights to calculate the cost of the hotel ?

[greatcornholio]

the program will be used to calculate the costs incurred on a project.
I put the command in trigger, only the sentence command when you press a button (I am doing the very basic program to work now, i will Improve it later)

ahhh did not know the insert q could be done with inner joins lol
also having a field that is the total project sum of the values ​​would have to do the same, or that is done

(100 +100 + 20 + alojamiento.valor_habitacion + aerolineas.costo * * 20)

?

would be:
INSERT INTO costs
SELECT 1,20,1,1,100,100,1,1, (100 +100 + 20 + alojamiento.valor_habitacion aerolineas.costo * * 20) FROM airlines
ON INNER JOIN cost airlines costos.id_aerolinea = = id_aerolinea
ON INNER JOIN costos.id_alojamiento = alojamientos.id_alojamiento accommodation

thanks for the help:)

[adam_k]

I don't know what you mean by a trigger or a sentence command when you press a button. The trigger I was refering to is a table trigger which fires after (or instead of) and update/delete/insert.

To get the total sum you'd have to do something like:

select project_id, sum(cost) as 'total_cost' from costos group by project_id

If you are going to insert with this then you can use it like above, but if you want to update then you have to do it like this (assuming you're updating the projects table):

update projects 
set total_cost = total 
from projects inner join 
(select project_id,sum(cost) as 'total' from costos
group by project_id) cost 
on projects.project_id = cost.project_id 
where project_id = 'value' --you don't have to use the where, but it's here if you do

[greatcornholio]

By sentence command when pressing a button is just the INSERT INTO.... comand, that I would be using in the program im doing. Im not using or calling a trigger.

And for now im not updating the projects table, just using it to be able to create a cost record