Combine multiple queries into single query

oracleplsql

I am trying to run the following query: 

SELECT Freight.Freight_No,
       Freight.Available,
       Locomotive.Loco_No, 
       Locomotive.Available,
       Driver.Driver_No, 
       Driver.Name, 
       Driver.Available 
FROM Freight,Locomotive, Driver
WHERE Freight.Available = 'Y'
  AND Locomotive.Available = 'Y'
  AND Driver.Available = 'Y'
GROUP BY Freight.Freight_No, Locomotive.Loco_No, Driver.Driver_No;

What I am trying to do is check available freight wagons, locomotives and drivers. I can individually run a SELECT statements. However I want to combine into one query. I understand I can do this using UNION but I may not be going about it the correct way.

Tables

Freight

CREATE TABLE Freight
(
  Freight_No integer PRIMARY KEY, Serial_No integer UNIQUE, 
  Freight_Wagon_Type_Name varchar(20) 
    REFERENCES Freight_Wagon_Type(Freight_Wagon_Type_Name),
  Available varchar2(1) 
     CONSTRAINT AvailableFreight CHECK(Available IN('Y','N')) NOT NULL
);

Locomotive

CREATE TABLE Locomotive
(
  Loco_No integer PRIMARY KEY, 
  Serial_No integer UNIQUE, 
  Loco_Class_No integer 
    REFERENCES Locomotive_Class(Loco_Class_No), 
  Loco_name varchar2(20), 
  Available varchar2(1) 
     CONSTRAINT Available CHECK(Available IN('Y','N')) NOT NULL
);

Driver 

CREATE TABLE Driver
(
   Driver_No integer PRIMARY KEY, 
   Name varchar2(20),
   Available varchar2(1) 
     CONSTRAINT AvailableDriver CHECK(Available IN('Y','N')) NOT NULL
);

Best Answer

You can use UNION [ALL] to combine rows from multiple queries, as long as the number of columns are the same and the columns are compatible, if not of the exact same types.

SELECT 'Driver' as SourceTable, Driver_No, Name, Available 
  FROM Driver
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Freight', Freight_No, Freight_Wagon_Type_Name, Available
  FROM Freight
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Locomotive', Loco_No, Loco_Name, Available
  FROM Locomotive
 WHERE Available = 'Y';

(added SourceTable column per a_horse_with_no_name's suggestion)

Note that the column names are taken from the first query in the UNION, which means you end up with (SourceTable, Driver_No, Name, Available). You can always alias them as required.

Related Question