error: column "car_id" must appear in the GROUP BY clause or be used in an aggregate funct-CodePudding

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?

CodePudding user response：

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
  `);