dimanche 28 juin 2015

Beginner learning sql joins in mySQL

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