SQLTeam.com | Weblogs | Forums

Combining several sql-queries into one


#1

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

Any idea how I can do this?


#2

do u have any Primary Key or Forgein key on tables .. Monarch ???


#3

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


#4

Yes Here is how I created the tables and the primary and foreign keys.

CREATE DATABASE Visbyflyg;

CREATE TABLE Flygplan
(
Namn VarChar(255) NOT NULL,
Beteckning VarChar(255),
Platser Integer,
primary key (Namn)
);




CREATE TABLE Flight
(
Flightnummer VarChar(50) NOT NULL,
Namn VarChar(255),
Resertill VarChar(255),
Reserfran VarChar(255),
Avgdatum Date NOT NULL,
Avgtid Time,
Ankdatum Date,
Anktid Time,
CONSTRAINT FlightPK
primary key (Flightnummer,Avgdatum),
CONSTRAINT FlightFlygplanFK
foreign key (Namn) references Flygplan(Namn)
ON DELETE SET NULL ON UPDATE CASCADE,
);


CREATE TABLE Biljetttyp
(
Biljettnr Integer NOT NULL IDENTITY,
Flightnummer VarChar(50),
Biljettyp VarChar(50),
Pris Integer,
primary key (Biljettnr)
);


CREATE TABLE Passagerare
(
Nr Integer NOT NULL IDENTITY, 
Namn VarChar(255),
Typ VarChar(30),
Passning Bit,
Platsnr Integer,
Biljettyp VarChar(255),
Flightnummer VarChar(50),
Avgdatum Date
CONSTRAINT PassagerarePK
primary key (Nr),
CONSTRAINT PassagerareFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)
ON DELETE CASCADE ON UPDATE CASCADE
);

#5

What is FP.IDEMP = F.IDEMPNO in your code?


#6

Ah,
I think i got it.
I now have

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?