Oracle SQL help

Question

Hi,
I posted on Friday (http://stackoverflow.com/questions/3997919/sql-multiple-count) and had a few responses.

Having tried to implement them today, I keep getting the same error.

My SQL code now is:

SELECT MBDDX_STUDY.STUDY_NAME,
       COUNT(MBDDX_EXPERIMENT.STUDY_ID)
AS     NUMBER_OF_EXPERIMENTS
FROM MBDDX_STUDY
INNER JOIN MBDDX_EXPERIMENT
       ON MBDDX_STUDY.ID = MBDDX_EXPERIMENT.STUDY_ID
INNER JOIN (SELECT COUNT(MBDDX_TREATMENT_GROUP.GROUP_NO)
            FROM MBDDX_TREATMENT_GROUP)
       ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID
GROUP BY MBDDX_STUDY.STUDY_NAME

I keep getting the error:

ORA-00904: "MBDDX_TREATMENT_GROUP"."STUDY_ID": invalid identifier

Is it because it is outside of the inner join bracket, i.e. out of scope? I am very new to SQL and cannot understand why it wont work. I can get it working using select subqueries (without joins) but I want to also be able to work with joins.

If it matters any I am using Toad for Oracle.

Thanks.

Answer 1

Because you join with a query. Give a name to that query, and refer to it that way:

SELECT MBDDX_STUDY.STUDY_NAME
     , COUNT ( MBDDX_EXPERIMENT.STUDY_ID )
AS     NUMBER_OF_EXPERIMENTS
  FROM MBDDX_STUDY
 INNER JOIN MBDDX_EXPERIMENT
    ON MBDDX_STUDY.ID = MBDDX_EXPERIMENT.STUDY_ID
 inner JOIN ( SELECT study_id, COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
            FROM MBDDX_TREATMENT_GROUP  group by study_id ) AS my_query
    ON my_query.STUDY_ID = MBDDX_STUDY.ID
 GROUP BY MBDDX_STUDY.STUDY_NAME

Answer 2

For one thing, a subquery must have an alias. Change:

inner JOIN ( SELECT COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
    FROM MBDDX_TREATMENT_GROUP )
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

to

inner JOIN ( SELECT COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
    FROM MBDDX_TREATMENT_GROUP ) as CountAlias
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

The second thing is that you have to include all columns you plan to use. Right now, the subquery just selects a count, but the ON clause references STUDY_ID. You can fix that by including STUDY_ID in the subquery select list, like:

inner JOIN ( 
    SELECT  STUDY_ID
    ,       COUNT(MBDDX_TREATMENT_GROUP.GROUP_NO) as GroupCount
    FROM MBDDX_TREATMENT_GROUP) as CountAlias
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

Now after that, you might hit other issues, but I'm hoping this will get you started.