0

I have the following tables:

CREATE TABLE CUSTOMERS
(customerID     INT     PRIMARY KEY,
customerFullName        VARCHAR(20) NOT NULL);

CREATE TABLE VEHICLES
(vehicleVIN     VARCHAR(25) PRIMARY KEY,
vehicleMake     VARCHAR(15) NOT NULL);

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
customerID      INT,
vehicleVIN      VARCHAR(25));
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID),
CONSTRAINT SALES_FK2 FOREIGN KEY (vehicleVIN) REFERENCES VEHICLES(vehicleVIN));

And built the following query to run off of these tables:

SELECT VEHICLES.vehicleMake, CUSTOMERS.customerFullName
FROM SALES
JOIN CUSTOMERS on SALES.customerID = CUSTOMERS.customerID
    JOIN(
        SELECT SALES.vehicleVIN, VEHICLES.vehicleMake
        FROM SALES
          JOIN VEHICLES ON SALES.vehicleVIN = VEHICLES.vehicleVIN
          GROUP BY SALES.vehicleVIN, VEHICLES.vehicleMake
          HAVING COUNT(SALES.vehicleVIN) >= ALL
          (SELECT COUNT(SALES.vehicleVIN)
             FROM SALES
               INNER JOIN VEHICLES ON SALES.vehicleVIN=VEHICLES.vehicleVIN
               GROUP BY VEHICLES.vehicleMake))
      VEHICLES ON SALES.vehicleVIN = VEHICLES.vehicleVIN
      ORDER BY CUSTOMERS.customerFullName;

When I run this in SQL Fiddle, it executes perfectly and provides the correct output (one column that displays the vehicleMake that was purchased the most, and another column that displays the customerFullName of everyone who purchased that vehicleMake).

When I run it in NOVA (Oracle), It just says "No Rows Selected" Is there a difference in syntax between the two? Do I need to change my code? Thanks!

StevenC
  • 109
  • 1
  • 20
  • Are you sure both connected to the same DB? and in SQL "HAVING COUNT(SALES.vehicleVIN) >= ALL" looks wrong count () need to compare with numeric value! – Ramki Feb 01 '18 at 03:37
  • No, I'm not sure. Most likely not actually, since the NOVA DB is through school. I've used the HAVING COUNT (*) >= ALL in other queries and it works fine. The ALL is just in place for the numerical count in the proceeding SELECT Subquery. At least that's how I "understand" it. It was weird to me at first as well, until I saw it work! – StevenC Feb 01 '18 at 04:20

1 Answers1

0

Answered myself. I was pulling the data with a minor discrepancy in my insert statements between SQL Fiddle and NOVA. The issue was my fault, and from what I can tell now, all is performing as it should.

My insert statements in SQL fiddle were re-using a few vehicleVIN numbers, which was contributing to the success of the query that I had written, however the query is not correct for what I am trying to accomplish. Opened another question to discuss that specific issue.

Bottom line: I do not believe they are any major differences between the NOVA DB syntax and the SQL Fiddle syntax. All is good!

StevenC
  • 109
  • 1
  • 20