SQL inner join and subquery help

[dstockman]

The following code sends an error that it cannot find column BMI. Can anyone see my error? I am using OO3.3 with integrated HSQL.

Code: Select all

SELECT B.BMI_GROUP, B."Number", 100.0 * (CAST(B."Number" AS 'FLOAT') / CAST(R."Total" AS 'FLOAT')) AS "Percent"  FROM
	(SELECT BMI_GROUP, COUNT(BMI_GROUP) AS "Number" FROM
		(SELECT CASE WHEN BMI BETWEEN 1 AND 29.9 THEN '<30'
						WHEN BMI BETWEEN 30 AND 39.9 THEN '30-40'
						ELSE '40+' END AS BMI_GROUP
		FROM DEMOGRAPHICS WHERE AGE > 17) AS B
	GROUP BY BMI_GROUP) AS B 
INNER JOIN (SELECT COUNT(*) AS "Total" 
				FROM DEMOGRAPHICS WHERE AGE > 17) AS R ON R.BMI = B.BMI

The following code that groups by Race then by BMI works correctly.

Code: Select all

SELECT B.RACE, B.BMI_GROUP, B."Number", 100.0 * (CAST(B."Number" AS 'FLOAT') / CAST(R."RaceTotal" AS 'FLOAT')) AS "Percent"  FROM
	(SELECT RACE, BMI_GROUP, COUNT(RACE) AS "Number" FROM
		(SELECT RACE, CASE WHEN BMI BETWEEN 1 AND 29.9 THEN '<30'
						WHEN BMI BETWEEN 30 AND 39.9 THEN '30-40'
						ELSE '40+' END AS BMI_GROUP
		FROM DEMOGRAPHICS WHERE AGE > 17) AS B
	GROUP BY RACE, BMI_GROUP) AS B 
INNER JOIN (SELECT RACE, COUNT(RACE) AS "RaceTotal" 
				FROM DEMOGRAPHICS WHERE AGE > 17
				GROUP BY RACE) AS R ON R.RACE = B.RACE
				ORDER BY B.RACE, B.BMI_GROUP

I appreciate any help others can offer. Can someone suggest a reference to help me learn more about joins and subqueries? I have looked at a bunch of links google offered, but they keep everything basic. I fear I am not extending the simple examples to the more complex examples.

Doug

[FJCC]

Let's see if I follow this correctly. Your innermost Select

Code: Select all

(SELECT CASE WHEN BMI BETWEEN 1 AND 29.9 THEN '<30'
                  WHEN BMI BETWEEN 30 AND 39.9 THEN '30-40'
                  ELSE '40+' END AS BMI_GROUP
      FROM DEMOGRAPHICS WHERE AGE > 17) AS B

returns a single column called BMI_GROUP which contains one of three values (<30, 30-40, 40+).
The next select

Code: Select all

(SELECT BMI_GROUP, COUNT(BMI_GROUP) AS "Number" ...

returns two columns, BMI_GROUP and COUNT(BMI_GROUP), the second one being labeled "Number". This SELECT is the outermost one labeled B.
After the INNER JOIN you have the SELECT

Code: Select all

(SELECT COUNT(*) AS "Total"
            FROM DEMOGRAPHICS WHERE AGE > 17)

which just returns the count of all rows in the DEMOGRAPHICS table and is called R. But the condition of the INNER JOIN is that R.BMI = B.BMI. Neither R nor B has a column called BMI. B's two columns are called BMI_GROUP and Number. I suppose R's one column would be called COUNT(*). In your query that works, both the B and the R subqueries return a column called RACE, and the ON condition can be met.