Q:

MYSQL Not all inserted values being displayed

I should be able to show customer name, pet name, procedures, costs and total costs in one SQL SELECT statement. I am only getting one row of values, of which one of the values is NULL. I can't figure out what is wrong with my SQL statement or tables that it is causing it to not return all of the inputted values.
Here is an image of what I am getting as a result: 

CREATE DATABASE IF NOT EXISTS vet;
USE vet;

CREATE TABLE IF NOT EXISTS customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(70)
    );

CREATE TABLE IF NOT EXISTS invoice (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_date DATE,
    customer_id INT ,
    CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS pet (
    pet_id INT PRIMARY KEY AUTO_INCREMENT,
    pet_name VARCHAR(50),
    customer_id INT,
    CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS procedures (
    procedure_id INT PRIMARY KEY AUTO_INCREMENT,
    procedure_name VARCHAR(70),
    amount DECIMAL
);

CREATE TABLE IF NOT EXISTS invoice_pet (
    invoice_id INT,
    pet_id INT,
    CONSTRAINT FOREIGN KEY (invoice_id) REFERENCES invoice(invoice_id),
    CONSTRAINT FOREIGN KEY (pet_id) REFERENCES pet(pet_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS procedures_pet (
    procedure_id INT,
    pet_id INT,
    CONSTRAINT FOREIGN KEY (procedure_id) REFERENCES procedures(procedure_id),
    CONSTRAINT FOREIGN KEY (pet_id) REFERENCES pet(pet_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT INTO invoice (invoice_date) VALUES ('2004-04-05');
INSERT INTO invoice (invoice_date) VALUES ('2014-12-05');
INSERT INTO invoice (invoice_date) VALUES ('2009-08-29');
INSERT INTO invoice (invoice_date) VALUES ('2016-07-15');

INSERT INTO customer (customer_name) VALUES ('John Garett');
INSERT INTO customer (customer_name) VALUES ('Mary Wist');
INSERT INTO customer (customer_name) VALUES ('Beth Smith');
INSERT INTO customer (customer_name) VALUES ('Rick Sanchez');

INSERT INTO pet (pet_name, customer_id) VALUES ('Rover', 1);
INSERT INTO pet (pet_name, customer_id) VALUES ('Max', 3);
INSERT INTO pet (pet_name, customer_id) VALUES ('Munchie', 4);
INSERT INTO pet (pet_name, customer_id) VALUES ('Dixon', 2);
INSERT INTO pet (pet_name, customer_id) VALUES ('Lucky', 4);

INSERT INTO procedures (procedure_name, amount) VALUES ('Rabies Vaccination', 30.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Sterilization', 190.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Dental Surgery', 120.00);
INSERT INTO procedures (procedure_name, amount) VALUES ('Cystotomy', 200.00);

INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (1, 1);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (2, 1);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (1, 4);
INSERT INTO invoice_pet (invoice_id, pet_id) VALUES (3, 2);

INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (1, 1);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (3, 2);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (4, 4);
INSERT INTO procedures_pet (procedure_id, pet_id) VALUES (2, 1);

SELECT inv.invoice_id, cust.customer_name, p.pet_name, pro.procedure_name, 
pro.amount AS cost, SUM(amount) AS totalcost
FROM vet.procedures pro LEFT JOIN vet.procedures_pet propet
ON pro.procedure_id = propet.procedure_id
LEFT JOIN vet.pet p
ON propet.pet_id = p.pet_id
LEFT JOIN vet.invoice_pet invpet
ON p.pet_id = invpet.pet_id
LEFT JOIN vet.invoice inv
ON invpet.invoice_id = inv.invoice_id
LEFT JOIN vet.customer cust
ON inv.customer_id = cust.customer_id

A:

The following gives each customer, their pets, procedures those pets have had (if any) and the cost of those procedures.
You don't say what you mean by "total cost" (total per customer? per pet? grand total?) so I have gone with cost per customer (see correlated sub query)
SELECT    c.customer_name,
          p.pet_name,
          pr.procedure_name,
          pr.amount,
          (
            SELECT  SUM(amount)
            FROM    procedures pr1
                    JOIN procedures_pet pp1
                      ON pr1.procedure_id = pp1.procedure_id
                    JOIN pet p1
                      ON p1.pet_id = pp1.pet_id
                    JOIN customer c1
                      ON p1.customer_id = c1.customer_id
            WHERE c1.customer_id = c.customer_id
          ) AS totalcost
FROM      customer c
          JOIN pet p
              ON c.customer_id = p.customer_id
          LEFT JOIN procedures_pet pp
              ON pp.pet_id = p.pet_id
          LEFT JOIN procedures pr
              ON pr.procedure_id = pp.procedure_id
ORDER BY customer_name,
         pet_name,
         procedure_name;

