Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
SQL
create table customer (
CustomerID int primary key,    
FtName varchar (48), 
LtName varchar(48), 
Email varchar (75),
Town varchar (75), 
Parish varchar(75) 
);


create table Vehicle(
LicensePlate varchar(48) primary key,  /*Primary key for table Vehicle for License plate, variable can hold up to 48 characters*/
Make varchar (48),  
CarYear int 
);


create table Rental(
Date_of_Borrowing date,
CustomerID int,
Vehicle_LicenseN varchar (48),
Rental_Duration_Days int,
primary key (CustomerID,Vehicle_LicenseN), /*primary key from customer table and rental table*/
foreign key (CustomerID) references Customer(CustomerID),  /*foreign key constraint for customer table*/
foreign key(Vehicle_LicenseN) references Vehicle(LicensePlate) /*foreign key constraint for rental table*/
);



/* Create a query to show the customer ID, Name, Rental Date, licensePlate, duration, make, and year for all customers who rented a Toyota vehicle after 1998.*/


What I have tried:

select distinct customer.CustomerID,customer.FtName,customer.LtName, rental.Date_of_Borrowing, rental.Vehicle_LicenseN,rental.Rental_Duration_Days, vehicle.Make,vehicle.CarYear 
from rental, customer,vehicle
where vehicle.Make = 'toyota' and vehicle.CarYear > 1998;


/*It repeats the information in the table several times. Is there a way to fix this? */
Posted
Updated 19-Oct-22 19:27pm

1 solution

Use a JOIN instead of SELECT ... FROM Table1, Table2, Table3
The latter combines all rows from all tables, the former returns matching rows based on a relationship between them: SQL Joins[^]
 
Share this answer
 
Comments
chilly45 20-Oct-22 6:06am    
should i take out the word select and put Join instead? If so it brings up an error in syntax
OriginalGriff 20-Oct-22 8:40am    
No, read the info at the link I gave you - it explains what JOINS are, and gives examples of how to use them.
chilly45 20-Oct-22 9:04am    
On the website you sent me , it doesn't explain how to do the three table join . that's what i am not understanding.
OriginalGriff 20-Oct-22 9:58am    
You are kidding me ... you can't add a second join on the bottom of an example?
SELECT t1.X, t2.Y, t3.Z FROM Table1 t1
JOIN Table2 t2 ON ...
JOIN Table3 t3 ON ...
chilly45 20-Oct-22 14:23pm    
nope if i got it i wouldn't be asking , yes? thanks anyways.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900