So i want to make a select query that would output something like the information on this report, except the total costs.
And i have these tables in my DB (pets,service,employees,customers,appointment):
------------
tablestructure `appointment`
------------
CREATE TABLE IF NOT EXISTS `appointment` (
`petID` varchar(11) DEFAULT NULL,
`employeeID` varchar(11) DEFAULT NULL,
`serviceID` varchar(11) DEFAULT NULL,
`appointmentID` varchar(11) NOT NULL,
`appointmentDate` varchar(55) DEFAULT NULL,
PRIMARY KEY (`appointmentID`),
KEY `petID` (`petID`),
KEY `employeeID` (`employeeID`),
KEY `serviceID` (`serviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------
tablestructure `customers`
------------
CREATE TABLE IF NOT EXISTS `customers` (
`customerID` varchar(11) NOT NULL,
`customerType` int(1) DEFAULT NULL,
`customerName` varchar(55) DEFAULT NULL,
`street` varchar(55) DEFAULT NULL,
`city` varchar(55) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`zip` int(11) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`fax` varchar(20) DEFAULT NULL,
PRIMARY KEY (`customerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------
tablestructure `employees`
------------
CREATE TABLE IF NOT EXISTS `employees` (
`employeeID` varchar(11) NOT NULL,
`employeeFirstName` varchar(100) DEFAULT NULL,
`employeeLastName` varchar(100) DEFAULT NULL,
`degree` varchar(100) DEFAULT NULL,
`hireDate` varchar(55) DEFAULT NULL,
`street` varchar(100) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`zip` int(11) DEFAULT NULL,
`homePhone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`employeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------
tablestructure `pets`
------------
CREATE TABLE IF NOT EXISTS `pets` (
`petID` varchar(11) NOT NULL,
`petName` varchar(100) DEFAULT NULL,
`breed` varchar(100) DEFAULT NULL,
`animalType` varchar(100) DEFAULT NULL,
`dob` varchar(100) DEFAULT NULL,
`gender` varchar(11) DEFAULT NULL,
`owner` varchar(100) DEFAULT NULL,
PRIMARY KEY (`petID`),
KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------
tablestructure `service`
------------
CREATE TABLE IF NOT EXISTS `service` (
`customerID` varchar(11) DEFAULT NULL,
`petID` varchar(11) DEFAULT NULL,
`veterinarian` varchar(11) DEFAULT NULL,
`serviceID` varchar(11) NOT NULL,
`serviceName` varchar(100) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
PRIMARY KEY (`serviceID`),
KEY `customerID` (`customerID`),
KEY `petID` (`petID`),
KEY `veterinarian` (`veterinarian`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
.
Can anyone help me with this? Im a noob at SQL and this is making me crazy.