Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
);
share|improve this question
    
Why the group by you are not aggregating? You are also missing join conditions (preferrably using using standard SQL 92 JOINs) –  a_horse_with_no_name Apr 25 '13 at 11:23

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
You might want to add a column that identifies the "source" of each row to be able to distinguish between driver, freight and locomotive. –  a_horse_with_no_name Apr 25 '13 at 11:24
    
@a_horse_with_no_name this is exactly what I am trying to do. How would I go about doing this? –  David Apr 25 '13 at 11:26
1  
@a_horse_with_no_name did it by using 'table1' as table_name –  David Apr 25 '13 at 11:31
    
i think that if you'll wrap the union with parenthesis and add the condition once in the end, oracle will push the predicate and you will have a much more maintainable code. –  haki Apr 25 '13 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.