CODEWITHSUNDEEP
mysqlsqldatabase
Lukazs Pioetrszci
Lukazs Pioetrszci

Reputation: 275

Zero rows being returned, despite SQL Query

I am trying to display a table with details of some cars, even if they are not liked. In my case, example, no one likes the VW Golf but I still want to display the details in a table like one below:

carID   description     model   name        description     avgLikes
4       BMW Rules       VW      GERMANY     HatchBack       0.0

This is my schema of the DB:

CREATE TABLE IF NOT EXISTS Users(
    userID INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    forename VARCHAR(50) NOT NULL,
    surname VARCHAR(50) NOT NULL,
    PRIMARY KEY (userID)
);

CREATE TABLE IF NOT EXISTS CarType(
    carTypeID INT NOT NULL AUTO_INCREMENT,
    description VARCHAR(80),
    PRIMARY KEY (carTypeID)
);  

CREATE TABLE IF NOT EXISTS Country(
    countryID INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (countryID)
);  

CREATE TABLE IF NOT EXISTS Cars(
    carID INT NOT NULL AUTO_INCREMENT,
    carTypeID INT NOT NULL,
    countryID INT NOT NULL,
    description VARCHAR(100) NOT NULL,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100),
    FOREIGN KEY (carTypeID) REFERENCES CarType(carTypeID),
    FOREIGN KEY (countryID) REFERENCES Country(countryID),
    PRIMARY KEY (carID)
);

CREATE TABLE IF NOT EXISTS Likes(
    userID INT NOT NULL,
    carID INT NOT NULL,
    likes DOUBLE NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (carID) REFERENCES Cars(carID)
);

CREATE TABLE IF NOT EXISTS Sold(
    userID INT NOT NULL,
    carID INT NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (carID) REFERENCES Cars(carID)
);

INSERT INTO Users VALUES 
(NULL, "micheal", "Micheal", "Sco"),
(NULL, "bensco", "Ben", "Sco"),
(NULL, "shanemill", "Shane", "Miller");

INSERT INTO CarType VALUES
(NULL, "Saloon"),
(NULL, "HatchBack"),
(NULL, "Low Rider");

INSERT INTO Country VALUES
(NULL, "UK"),
(NULL, "USA"),
(NULL, "JAPAN"),
(NULL, "GERMANY");

INSERT INTO Cars VALUES
(NULL, 1, 2, "Ford Mustang lovers", "Mustang", "Ford"),
(NULL, 2, 3, "Drift Kings", "Skyline", "Nissan"),
(NULL, 3, 1, "British classic", "Cooper", "Mini"),
(NULL, 2, 4, "BMW Rules", "Golf", "VW");

INSERT INTO Likes VALUES
(1, 1, 3),
(1, 2, 2),
(2, 2, 5),
(2, 1, 7),
(2, 1, 1),
(2, 1, 2);

INSERT INTO Sold VALUES
(1, 2),
(1, 3),
(1, 1),
(2, 2),
(2, 3),
(3, 1),
(3, 3);

This is my SQL Query:

SELECT c.carID, c.description, c.model, cy.name, ct.description,
           l.avgLikes
    FROM Cars c INNER JOIN
         Country cy
         ON c.countryID = cy.countryID  AND cy.name = "Germany" INNER JOIN
         CarType ct
         ON c.carTypeID = ct.carTypeID LEFT JOIN
         (SELECT l.carId, AVG(Likes) as avgLikes
          FROM Likes l
          GROUP BY l.CarId
         ) l
         ON c.carID = l.carID

Nothing is being returned in my localhost phpmyadmin server. Basically, because there are no likes stored about this German car nothing is calculated and returned

Any help would be really appreciated

Thanks

Upvotes: 3

Views: 68

Answers (1)

Jonathan Clark
Jonathan Clark

Reputation: 1260

You could use a COALESCE on average likes - in other words, if no results are found in your average query, you will instead show 0.00 instead of NULL, as COALESCE will translate to the first non-null parameter.

I copied your database locally and tested with all country strings and got the expected output using:

SELECT c.carID, c.description, c.model, cy.name, ct.description,
           COALESCE(l.avgLikes,'0.00') AS 'avglikes'
    FROM Cars c 
    INNER JOIN Country cy ON c.countryID = cy.countryID  AND cy.name = "Germany" 
    INNER JOIN CarType ct ON c.carTypeID = ct.carTypeID 
    LEFT JOIN
         (SELECT l.carId, AVG(Likes) as avgLikes
          FROM Likes l
          GROUP BY l.CarId
         ) l
         ON c.carID = l.carID

Simple, but hopefully successful solution for you.

Upvotes: 1

Related Questions