I'm trying to do a subquery correlated, but not getting the right answer. I'm tried query 'suv' vehicles and inside this suv vehicles I would like vehicles made by BMW, but I'm getting all vehicles that are SUV. What should i do?

-- create a table
CREATE TABLE Cars (
  id INTEGER PRIMARY KEY,
  CarName varchar(50),
  vehicle_type varchar(50),
  Automaker varchar(50),
  Price INTEGER 
  
);
-- insert some values
INSERT INTO Cars VALUES 
(1, 'BMW X6',  'SUV','BMW', 108200),
(2, 'BMW X6',  'SUV','BMW',88200),
(3,'Grand Cherokee','SUV','JEEP' ,21678),
(4,'Grand Cherokee','SUV','JEEP' , 24518),
(5,'Grand Cherokee','SUV','JEEP' , 27548),
(6,'BMW 320','Sedan','BMW' , 54518),
(7,'AUDI A8','Sedan','AUDI' , 64518);

SELECT * FROM Cars WHERE vehicle_type =  (SELECT vehicle_type from Cars WHERE Automaker = 'BMW');

CodePudding user response：

The question as asked doesn't quite make sense, because the code at the bottom could be written to make this MUCH simpler... no subquery required:

SELECT * 
FROM Cars 
WHERE vehicle_type = 'SUV' AND Automaker = 'BMW';

To make it more interesting, let's pretend you want to get all cars of the same type as any car made by BMW. In this case, we see that BMW makes SUVs and Sedans, so we want to get all of those, but we want 'BMW' as the only input to the query.

I chose that, because it more closely resembles the original query. I think it will help you learn if you see what this code was actually doing. However, we do need two adjustments:

SELECT * 
FROM Cars 
WHERE vehicle_type IN  (SELECT DISTINCT vehicle_type from Cars WHERE Automaker = 'BMW');

The only changes where replacing = with IN and adding a DISTINCT to the subquery.

CodePudding user response：

You can write like :-

SELECT * FROM Cars WHERE vehicle_type = 'SUV' and Automaker='BMW';

this is give the all the details of cars tabel having type='suv' and vehicles made by BMW