I have learnt a lot about SQL but I am still struggling how to combine several sql queries into one.
I have the following queries
SELECT Flightnummer, Namn, Avgdatum, Reserfran, Resertill FROM Flight WHERE Reserfran = 'Paris' AND Resertill ='London'
SELECT Namn, Platser FROM Flygplan WHERE Namn = 'Hera'
SELECT Namn, Platser FROM Flygplan WHERE Namn = 'Poseidon'
SELECT Flightnummer, COUNT(*) AS bokade FROM Passagerare WHERE Flightnummer = 'AF330'
GROUP BY Flightnummer
SELECT Flightnummer, COUNT(*) AS [bokade] FROM Passagerare WHERE Flightnummer = 'BA801'
GROUP BY Flightnummer
That gives med the outputs:
Flightnummer Namn Avgdatum Reserfran Resertill
AF330 Hera 2013-08-01 Paris London
BA801 Poseidon 2013-08-01 Paris London
And
Namn Platser
Hera 220
And
Namn Platser
Poseidon 110
And
Flightnummer bokade
AF330 1
And
Flightnummer bokade
BA801 2
But after the first output I want to create a column that shows "spaces left", that is the number of Platser for each flight minus the value of Bokade
i DONT YOU TABLE DESIGN AND ever i dont know you Primary keys on each tables but here is the script which you will get the idea how can we join the tables and get the result Thanks
select COUNT(P.FLIGHTNUMNER), f.Flightnummer, f.Namn, f.Avgdatum, f.Reserfran, f.Resertil, FP.PLATSER from flight f
inner join Flygplan FP ON FP.IDEMP = F.IDEMPNO
INNER JOIN Passagerare P ON P.Flightnummer.F.Flightnummer
WHERE Namn IN ( 'Hera','Poseidon')
AND Flightnummer IN ('AF330','BA801')
GROUP BY f.Flightnummer, f.Namn, f.Avgdatum, f.Reserfran, f.Resertil, FP.PLATSER
select COUNT(Passagerare.Flightnummer) AS inbokade, Flight.Flightnummer, Flight.Namn, Flight.Avgdatum, Flight.Reserfran, Flight.Resertill, Flygplan.platser from Flight
INNER JOIN Flygplan ON Flygplan.Namn = Flight.Namn
INNER JOIN Passagerare ON Passagerare.Flightnummer = Flight.Flightnummer
WHERE Flygplan.Namn IN ( 'Hera','Poseidon')
AND Flight.Flightnummer IN ('AF330','BA801')
GROUP BY Flight.Flightnummer, Flight.Namn, Flight.Avgdatum, Flight.Reserfran, Flight.Resertill, Flygplan.Platser;
that gives the output
inbokade Flightnummer Namn Avgdatum Reserfran Resertill platser
1 AF330 Hera 2013-08-01 Paris London 220
2 BA801 Poseidon 2013-08-01 Paris London 110
The last thing I want to do is create another column that calculates the value from platser minus the value from inbokade for each row.
How can i integrate the subtraction in the sql query?