CODEWITHSUNDEEP
sqldatabasems-accesssubquery
Batman
Batman

Reputation: 6353

SQL - Combining two queries

I have these two Queries I'm trying to combine.

Query 1 - This tells me all the vehicles I have that are in my inventory. Meaning are not in the ownership table: 

SELECT VEHICLE.*
FROM VEHICLE
WHERE NOT EXISTS
(SELECT NULL FROM OWNERSHIP WHERE VEHICLE.VEH_ID= OWNERSHIP.VEH_ID);

Query 2 - This one tells me which car is the highest priced for each brand. 

SELECT B.BRAND_ID, B.BRAND_NAME, M.MODEL_NAME, C.CLASS_NAME, V.VEH_ID, V.VEH_YEAR, V.VEH_PRICE

FROM (((VEHICLE AS V INNER JOIN CLASS AS C ON V.CLASS_ID = C.CLASS_ID) 
INNER JOIN MODEL AS M ON M.MODEL_ID = V.MODEL_ID) 
INNER JOIN BRAND AS B ON B.BRAND_ID = M.BRAND_ID) 
INNER JOIN (SELECT M.BRAND_ID, MAX(V.VEH_PRICE) AS VEH_PRICE FROM VEHICLE AS V 
INNER JOIN MODEL AS M ON M.MODEL_ID = V.MODEL_ID GROUP BY M.BRAND_ID) 

AS derived ON (v.VEH_PRICE = derived.VEH_PRICE) AND (b.BRAND_ID = derived.BRAND_ID)
ORDER BY 7 DESC;

I realized that determining which vehicles are the most expensive ones won't matter if they are already owned by a customer. Since the first query tells us which are available, how would I combine these two?

Upvotes: 0

Views: 128

Answers (1)

fthiella
fthiella

Reputation: 49049

I couldn't test your query, but think this is what you are looking for:

SELECT
  B.BRAND_ID,
  B.BRAND_NAME,
  M.MODEL_NAME,
  C.CLASS_NAME,
  V.VEH_ID,
  V.VEH_YEAR,
  V.VEH_PRICE
FROM 
  (((VEHICLE AS V INNER JOIN CLASS AS C ON V.CLASS_ID = C.CLASS_ID) 
    INNER JOIN MODEL AS M ON M.MODEL_ID = V.MODEL_ID) 
   INNER JOIN BRAND AS B ON B.BRAND_ID = M.BRAND_ID) 
  INNER JOIN (SELECT M.BRAND_ID, MAX(V.VEH_PRICE) AS VEH_PRICE
              FROM VEHICLE AS V
                   INNER JOIN MODEL AS M
                   ON M.MODEL_ID = V.MODEL_ID
              WHERE
                NOT EXISTS
                  (SELECT NULL FROM OWNERSHIP
                   WHERE V.VEH_ID=OWNERSHIP.VEH_ID)
              GROUP BY M.BRAND_ID) AS derived
  ON (v.VEH_PRICE = derived.VEH_PRICE)
  AND (b.BRAND_ID = derived.BRAND_ID)
WHERE
  NOT EXISTS
  (SELECT NULL FROM OWNERSHIP
   WHERE V.VEH_ID=OWNERSHIP.VEH_ID)
ORDER BY 7 DESC;

You have to exclude cars already owned in the subquery where you calculate the maximum price for each brand, but also in the outer query, to exclude owned cars that have the same price as the maximum of cars that don't have an owner.

Instead of using NOT EXISTS clause, i would also suggest to add one more LEFT JOIN with OWNERSHIP both in the subquery and in the outer query, and to take only the rows where OWNERSHIP.VEH_ID is null.

Upvotes: 2

Related Questions