Oracle SQL Developer - JOIN on 2 queries with a one-to-many relationship

Question

I have two queries that I'm trying to join together.

In first_query TABLE2.PROCESS_ID, every PROCESS_ID is unique in that table. In second_query though there are several PROCESS_ID's with the same number in TABLE3, so I think I have to do a one-to-many join. The join_query I have is giving me an error ORA-00933: SQL command not properly ended which I'm assuming has something to do with the one-to-many relationship with the JOIN.

I'm not really sure how to resolve this. Any help would be much appreciated!

first_query = """
SELECT TABLE1.RULE_ID, TABLE2.STATUS, TABLE2.ERROR_MESSAGE, TABLE2.PROCESS_ID
FROM TABLE2 LEFT JOIN
     TABLE1
      ON TABLE1.RULE_ID = TABLE2.RULE_ID 
WHERE TABLE1.RULE_NAME IN ('TEST1', 'TEST2')   
"""

second_query = """
SELECT RECORDS_PROCESSED, PROCESS_ID, STATUS
FROM TABLE3
"""

join_query = """
SELECT RULE_ID, STATUS, ERROR_MESSAGE, PROCESS_ID
FROM (first_query) as query_1
INNER JOIN (second_query) as query_2
ON query_1.PROCESS_ID = query_2.PROCESS_ID 
GROUP BY PROCESS_ID desc
"""

Answer 1

You can not select 4 columns and group by only one of them unles you include selected columns as part of aggregation fucntion(like max(), sum(),...). One of the options is this:

SELECT query_1.RULE_ID         --1
       , query_2.STATUS        --2
       , query_1.ERROR_MESSAGE --3
       , query_1.PROCESS_ID    --4
FROM (SELECT TABLE1.RULE_ID
             , TABLE2.STATUS
             , TABLE2.ERROR_MESSAGE
             , TABLE2.PROCESS_ID
      FROM TABLE2 
      LEFT JOIN TABLE1
      ON TABLE1.RULE_ID = TABLE2.RULE_ID 
      WHERE TABLE1.RULE_NAME IN ('TEST1', 'TEST2')) query_1
INNER JOIN (SELECT RECORDS_PROCESSED
                   , PROCESS_ID
                   , STATUS
            FROM TABLE3) query_2
ON query_1.PROCESS_ID = query_2.PROCESS_ID 
GROUP BY query_1.RULE_ID
         , query_2.STATUS
         , query_1.ERROR_MESSAGE
         , query_1.PROCESS_ID

Also please do consider using aliases like this(in your first query):

SELECT T1.RULE_ID
       , T2.STATUS
       , T2.ERROR_MESSAGE
       , T2.PROCESS_ID
FROM TABLE2 T2 
LEFT JOIN TABLE1 T1 ON T1.RULE_ID = T2.RULE_ID 
WHERE T1.RULE_NAME IN ('TEST1', 'TEST2')   

Also, apply the same logic with aliases on your final query or else you will have a different kind of error : "ORA-00918: column ambiguously defined"

Here is a small demo