How to add one column of an intermediate table to the final result table-CodePudding

I'm a SQL newbie here. I've encountered the task to output a table that contains two columns: Service name and a count(the number of time this service is requested).

Now I have a n intermediate table which contains two columns as well. Instead of the service name column, it has a service ID column which can be used to retrieve service names from a Service table.

I'm wondering how to keep the count column so that I can add it in the final result, after translating the IDs to their names. I tried to use With (...) As to store the intermediate table, but Oracle does not seem to recognize the syntax. Any help is appreciated. Thanks!

CodePudding user response：

This query will allow you to make a third table based on the first two tables.

CREATE TABLE table3
AS
SELECT * FROM table1
INNER JOIN table2 ON table1.ServiceId = table2.ServiceId

CodePudding user response：

It would help if you posted what you really have (tables' description) and some sample data along with desired output.

This is how I understood it:

Sample data:

SQL> with
  2  service (service_id, name) as
  3    (select 1, 'Service 1' from dual union all
  4     select 2, 'Service 2' from dual
  5    ),
  6  intermediate (inter_id, service_id, datum) as
  7    (select 1, 1, date '2022-05-13' from dual union all
  8     select 2, 1, date '2022-04-28' from dual union all
  9     select 3, 1, date '2022-03-07' from dual
 10    )

Query begins here; it selects service name and number of times each service was requested. Tables are outer joined (because service #2 doesn't have any requests yet so you wouldn't get it at all if it were inner join):

 11  select s.name,
 12         count(i.inter_id) times_requested
 13  from service s left join intermediate i on i.service_id = s.service_id
 14  group by s.name
 15  order by s.name;

NAME      TIMES_REQUESTED
--------- ---------------
Service 1               3
Service 2               0

SQL>