I have created 3 tables: dog, customer and owner. It is a many to one relationship with many dogs owned by 1 customer.
CREATE TABLE dog
(
DogID int(6) NOT NULL,
DogName varchar(15),
medicalID int (6),
Gender character(1) check(gender in ('m', 'f')),
Age int(2),
Breed varchar(15),
size character (1) check(size in ('s', 'm', 'l')),
primary key (DogID));
CREATE TABLE Owns
(
DogID int (6)primary key,
CustomerID int (6),
foreign key (CustomerID) references customer (CustomerID),
foreign key (DogID) references dog (DogID));
CREATE TABLE customer
(
CustomerID int(6) NOT NULL,
FirstName varchar(15),
Surname varchar(15),
Address varchar(225),
email varchar (30),
TelNo varchar (15),
MobNo Varchar (15),
EmergencyName varchar (40),
EmergencyPhoneNo varchar(15),
primary key (CustomerID));
I have queried with but nothing is returned.
select dog.dogName
from dog, owns, customer
where owns.dogID = dog.dogId
and customer.customerID = owns.customerID
and customer.FirstName = "CustomersName";
Aucun commentaire:
Enregistrer un commentaire