I have some tables
all_cars(id, name, image, price)
cars_info(car_id, ps, engine, sit_place, fsk)
cars_details(car_id, info)
cars_price(car_id, time_zone, km, price)
I want to join all tables like this:
const d = await pool.query(`
SELECT
all_cars.id,
all_cars.name,
all_cars.image,
all_cars.price,
cars_info.car_id,
cars_info.ps,
cars_info.engine,
cars_info.sit_place,
cars_info.fsk,
cars_details.car_id,
cars_details.info,
cars_price.car_id,
cars_price.time_zone,
cars_price.km,
cars_price.price
FROM all_cars
INNER JOIN cars_info
ON all_cars.id = cars_info.car_id
INNER JOIN cars_details
ON all_cars.id = cars_details.car_id
INNER JOIN cars_price
ON all_cars.id = cars_price.car_id
GROUP BY all_cars.id
`);
I’m getting the following error:
error: column "car_id" must appear in the GROUP BY clause or be used in an aggregate function
And if I group by car_id then I got group ps, then I group by ps then I got the others.
If I dont group it I get 5x the same row. If not I got error.
Why I get this error? Is there a better way to do this and how can I solve this error above?
>Solution :
The clause GROUP BY doesn’t make any sense if there’s no aggregate function in your statement. You should rather use DISTINCT for your purpose, assuming you have duplicate records with respect to "if I dont group it I get 5x the same row".
const d = await pool.query(`
SELECT DISTINCT
all_cars.id,
all_cars.name,
all_cars.image,
all_cars.price,
cars_info.car_id,
cars_info.ps,
cars_info.engine,
cars_info.sit_place,
cars_info.fsk,
cars_details.car_id,
cars_details.info,
cars_price.car_id,
cars_price.time_zone,
cars_price.km,
cars_price.price
FROM all_cars
INNER JOIN cars_info
ON all_cars.id = cars_info.car_id
INNER JOIN cars_details
ON all_cars.id = cars_details.car_id
INNER JOIN cars_price
ON all_cars.id = cars_price.car_id
`);