SQL select all from one table of joint tables

Question

I am a SQL beginner and I need to figure out this query: I have three tables joined together from which I am counting certain value, like this:

SELECT SEATS_MAX-COUNT(BOOKING_ID) 
FROM FLIGHTS 
  INNER JOIN PLANES ON FLIGHTS.PLANE_ID=PLANES.PLANE_ID 
  LEFT JOIN BOOKINGS ON FLIGHTS.FLIGHT_ID=BOOKINGS.FLIGHT_ID 
GROUP BY SEATS_MAX;

This returns number of free seats in a flight. But I would like to get all the columns from FLIGHTS (as in SELECT * FROM FLIGHTS;) plus the count number. i.e. something like

SELECT FLIGHTS.*, SEATS_MAX-COUNT(BOOKING_ID) 
FROM FLIGHTS 
  INNER JOIN PLANES ON FLIGHTS.PLANE_ID=PLANES.PLANE_ID 
  LEFT JOIN BOOKINGS ON FLIGHTS.FLIGHT_ID=BOOKINGS.FLIGHT_ID 
GROUP BY SEATS_MAX;

but this doesn't work (invalid user.table.column, table.column or column specification). Is there a way to achieve this? I'm using Oracle db.

Thanks

Solution

In the group by you need to have all the column not aggregated.

So you query has to become:

      SELECT FLIGHTS.*, 
             SEATS_MAX-COUNT(BOOKING_ID) 
        FROM FLIGHTS 
  INNER JOIN PLANES 
          ON FLIGHTS.PLANE_ID = PLANES.PLANE_ID 
   LEFT JOIN BOOKINGS 
          ON FLIGHTS.FLIGHT_ID = BOOKINGS.FLIGHT_ID 
    GROUP BY FLIGHTS.Column1,
             ...
             FLIGHTS.ColumN,
             SEATS_MAX;

Edit: To list all columns of you table you can use the following query

  SELECT 'FLIGHTS.' || column_name
    FROM user_tab_columns
   WHERE table_name = 'FLIGHTS'
ORDER BY column_id;

This should make your life a bit easier, then copy and paste

Comments

[Martin Melka]

So there is no way I could easily say "list all columns from this table plus something else"? I have to explicitly name all the columns?

[mucio]

not really, you need to list them, I'll add a small query that can help you to have a list of all columns

[AndyDan]

that could work, but it depends on whether SEATS_MAX is in the FLIGHTS table or the PLANES table. It's not clear from the OP's query, but I suspect it's in PLANES.