Skip to content
Advertisement

Table shows DISTINCT information when I tell it not to?

For some reason I keep getting an output with DISTINCT information. I am trying to understand JOINS and I am not sure why I am getting this output:

MANUFACTURE                              MODEL         
---------------------------------------- ---------------
Chrysler Corp.                           200            
Ford Motor Company                       300            
General Motors                           200 Convertible
Honda                                    Durango        
Nisan                                    Avenger        
Toyota                                   Town & Country 
                                         4Runner        
                                         Altima         
                                         Caliber        
                                         Camry          
                                         Challenger     
                                         Charger        
                                         Colorado       
                                         Corolla        
                                         Cruze          
                                         EX37           
                                         Grand Caravan  
                                         Journey        
                                         Malibu         
                                         Orlando        
                                         Pathfinder     
                                         Prius          
                                         Q50            
                                         Q60            
                                         Q70            
                                         Rogue SL       
                                         Sonic          
                                         Versa Note     

 28 rows selected

Here is what i have tried and above is the output:

SELECT MANUFACTURENAME AS "MANUFACTURE", MODELNAME AS "MODEL"
FROM TBLMANUFACTURE
FULL JOIN TBLMODEL
ON TBLMANUFACTURE.MANUFACTUREID = TBLMODEL.MODELID
ORDER BY MANUFACTURENAME, MODELNAME ASC;

What I am trying to do is list all of the manufacturer names with their associated models.

Scripts to Repro the issue: 

DROP TABLE  tblOptionDetail;
DROP TABLE  tblOption;
DROP TABLE  tblVehicle;
DROP TABLE  tblModel;
DROP TABLE  tblBrand;
DROP TABLE  tblManufacture;


CREATE TABLE tblManufacture
(
ManufactureID NUMBER(38) PRIMARY KEY,
ManufactureName CHAR(40)
);

CREATE TABLE tblBrand
(
BrandID NUMBER(38) PRIMARY KEY,
ManufactureID NUMBER(38),
BrandName CHAR(40),
FOREIGN KEY (ManufactureID) REFERENCES tblManufacture(ManufactureID)
);

CREATE TABLE tblModel
(
ModelID NUMBER(38) PRIMARY KEY,
BrandID NUMBER(38),
ModelName CHAR(40),
FOREIGN KEY (BrandID) REFERENCES tblBrand(BrandID)
);

CREATE TABLE tblVehicle
(
VehicleID NUMBER(38) PRIMARY KEY,
ModelID NUMBER(38),
VehicleYear NUMBER(38) CHECK (VehicleYear BETWEEN 1900 and 3000),
VehicleKM NUMBER(38) CHECK (VehicleKM BETWEEN 0 and 100000000),
VehiclePrice NUMBER(38) CONSTRAINT carprice CHECK (VehiclePrice BETWEEN 0 and 1000000),
FOREIGN KEY (ModelID) REFERENCES tblModel(ModelID)
);

CREATE TABLE tblOption
(
OptionID NUMBER(38) PRIMARY KEY,
OptionDesc CHAR(40)
);

CREATE TABLE tblOptionDetail
(
OptionID NUMBER(38),
VehicleID NUMBER(38),
PRIMARY KEY (OptionID, VehicleID),
FOREIGN KEY (OptionID) REFERENCES tblOption(OptionID),
FOREIGN KEY (VehicleID) REFERENCES tblVehicle(VehicleID)
);


-- populate the Manufacture table

INSERT ALL 
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (1,'Chrysler Corp.')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(2,'General Motors')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(3,'Ford Motor Company')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(4,'Toyota')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (5,'Honda')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (6,'Nisan')
SELECT * FROM dual;

-- populate the Brand table

INSERT ALL
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (1,1, 'Chrysler')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (2,1, 'Dodge')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (3,1, 'Jeep')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (4,2, 'Chevrolet')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (5,2, 'Buick')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (6,2, 'Cadillac')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (7,2, 'GMC')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (8,4, 'Toyota')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (9,6, 'Nissan')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (10,6,'Infiniti')
SELECT * FROM dual;

-- populate the Model table

INSERT ALL
INTO tblModel (ModelID, BrandID, ModelName) VALUES (1,1, '200')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (2,1, '200 Convertible')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (3,1, '300')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (4,1, 'Town'||' & '||'Country')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (5,2, 'Durango')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (6,2, 'Avenger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (7,2, 'Challenger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (8,2, 'Charger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (9,2, 'Caliber')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (10,2, 'Grand Caravan')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (11,2, 'Journey')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (12,4, 'Sonic')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (13,4, 'Cruze')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (14,4, 'Orlando')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (15,4, 'Colorado')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (16,4, 'Malibu')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (17,8, '4Runner')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (18,8, 'Prius')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (19,8, 'Camry')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (20,8, 'Corolla')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (21,9,'Rogue SL')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (22,9, 'Versa Note')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (23,9, 'Pathfinder')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (24,9, 'Altima')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (25,10, 'Q70')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (26,10, 'Q50')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (27,10, 'Q60')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (28,10, 'EX37')
SELECT * FROM dual;

-- populate Option table

INSERT ALL 
INTO tblOption (OptionID,OptionDesc) VALUES (1,'Power locks')
INTO tblOption (OptionID,OptionDesc) VALUES (2,'Power windows')
INTO tblOption (OptionID,OptionDesc) VALUES (3,'Air conditioning')
INTO tblOption (OptionID,OptionDesc) VALUES (4,'Heated mirrors')
INTO tblOption (OptionID,OptionDesc) VALUES (5,'Remote keyless entry')
INTO tblOption (OptionID,OptionDesc) VALUES (6,'CD Player')
INTO tblOption (OptionID,OptionDesc) VALUES (7,'GPS')
INTO tblOption (OptionID,OptionDesc) VALUES (8,'In-dash DVD player')
INTO tblOption (OptionID,OptionDesc) VALUES (9,'Overhead DVD player')
INTO tblOption (OptionID,OptionDesc) VALUES (10,'Satellite radio')
INTO tblOption (OptionID,OptionDesc) VALUES (11,'MP3 player')
INTO tblOption (OptionID,OptionDesc) VALUES (12,'Antilock braking system')
INTO tblOption (OptionID,OptionDesc) VALUES (13,'Electronic stability system')
INTO tblOption (OptionID,OptionDesc) VALUES (14,'Traction control')
INTO tblOption (OptionID,OptionDesc) VALUES (15,'Cruise control')
INTO tblOption (OptionID,OptionDesc) VALUES (16,'Intelligent cruise control')
INTO tblOption (OptionID,OptionDesc) VALUES (17,'Parking assist system')
INTO tblOption (OptionID,OptionDesc) VALUES (18,'Xenon headlights')
INTO tblOption (OptionID,OptionDesc) VALUES (19,'Aluminum rims')
INTO tblOption (OptionID,OptionDesc) VALUES (20,'AWD')
INTO tblOption (OptionID,OptionDesc) VALUES (21,'Convertable')
INTO tblOption (OptionID,OptionDesc) VALUES (22,'Heated Seats')
SELECT * FROM dual;

-- populate Vehicle table

INSERT ALL
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (1,10, 2010, 45000, 18000)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (2,10, 2010, 65420, 17500)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (3,16, 2004, 143900, 3200)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (4,11, 2010, 38900, 14500)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (5,27,2014,17250,45999)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (6,25,2015,2900,62300)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (7,17,2010,87900,19800)
SELECT * FROM dual;

-- populate OptionDetail table

INSERT ALL
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (7, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (8, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (11, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (11, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES  (4,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (8,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (21,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (22,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (4,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,7)
SELECT * FROM dual;

COMMIT;

Advertisement

Answer

You have to include tblBrand to link the manufacture with model. So regular inner joins:

SELECT MANUFACTURENAME AS "MANUFACTURE", MODELNAME AS "MODEL"
FROM TBLMANUFACTURE 
join tblBrand ON TBLMANUFACTURE.ManufactureID = tblBrand.ManufactureID 
JOIN TBLMODEL ON tblBrand.BrandID= TBLMODEL.BrandID
ORDER BY MANUFACTURENAME, MODELNAME ASC;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement