CODEWITHSUNDEEP
mysqlsqljoin
KingDave212
KingDave212

Reputation: 163

Joining 2 tables, need to display all fields based on highest price, with no duplicates from a different field

I need to show all fields on the most expensive car of each manufacturer.

The two tables being used are:

CREATE TABLE CARS
(
Vehicle_Identification_Number int(10) NOT NULL UNIQUE,
Manufacturers_ID int(5),
Owner_ID int(10),
Model varchar(25),
Manufaturer_Year int(4),
Mileage int(10),
Price int(10),
PRIMARY KEY (Vehicle_Identification_Number),
FOREIGN KEY (Manufacturers_ID) REFERENCES MANUFACTURERS (Manufacturers_ID),
FOREIGN KEY (Owner_ID) REFERENCES OWNERS (Owner_ID)
)
ENGINE= innodb;

CREATE TABLE MANUFACTURERS
(
Manufacturers_ID int(5) UNIQUE,
Name varchar(15) UNIQUE,
City varchar(30),
State char(2),
Zip char(5),
Phone char(10),
PRIMARY KEY (Manufacturers_ID)
)
ENGINE= innodb;

What I have working so far is:

SELECT *
FROM MANUFACTURERS
 LEFT JOIN CARS
  ON MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
UNION
SELECT *
FROM MANUFACTURERS
 RIGHT JOIN CARS
  ON MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
ORDER BY Price DESC;

Here is where I am stuck, everything I have tried left me with an error message. Any help would be appreciated.

Upvotes: 0

Views: 28

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

I'm not sure what you mean by "no duplicates from a different field".

The highest price for each manufacturer is this set. (There might be more than one car at the highest price.)

select Manufacturers_ID, max(Price)
from cars
group by Manufacturers_ID;

Join on both columns in that set. (Second inner join, below.)

select MANUFACTURERS.*, CARS.*
from CARS
inner join MANUFACTURERS 
        on MANUFACTURERS.Manufacturers_ID = CARS.Manufacturers_ID
inner join (select Manufacturers_ID, max(Price) as Price
            from cars
            group by Manufacturers_ID) as MOST_EXPENSIVE
        on CARS.Manufacturers_ID = MOST_EXPENSIVE.Manufacturers_ID
       and CARS.Price = MOST_EXPENSIVE.Price;

Upvotes: 1

Related Questions