I'm looking to combine two fields (ids) in a query that correspond to two different lookup tables in the database. I have this so far:

SELECT e.id, e.name, r.id, r.name
FROM
(select isnull(ethnicity_id, 3), isnull(race_id, 7), count(eir.id) as total
from enrollment_info_real eir)
join ethnicity e on (e.id = eir.ethnicity_id)
join race r on (r.id = eir.race_id)
group by ethnicity_id, race_id

I would like the select isnull... query to get me a data set that does not have nulls and then be able to join the lookup tables based on this data set. Any ideas on how to fix this? I keep getting the error message:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'join'.

Thanks

wow, that's too confusing for me

in the outer query, you cannot have non-aggregate columns in the SELECT list which aren't in the GROUP BY

in the inner query, you can't have both aggregate and non-aggregate columns in the SELECT list if you don't have a GROUP BY

the derived table alias eir goes outside the parentheses

i have no clue what you're trying to do but perhaps this will point you in the right direction --

What does coalesce do?

Does this look right if I wanted to make a grid of sorts combining a person's race with ethnicity? I want to show how many people are white/hispanic or latino, white/non-hispanic or latino, etc.

select count(e.id), e.name, count(r.id), r.name
from enrollment_info_real
inner
join ethnicity e
on coalesce(enrollment_info_real.ethnicity_id,3)
= e.id
inner
join race r
on coalesce(enrollment_info_real.race_id,7)
= r.id
group
by e.id, e.name, r.id, r.name

coalesce finds a non-null value within its list of values

so coalesce(enrollment_info_real.race_id,7) means that if enrollment_info_real.race_id is null, then 7 is used

as for your grouping, i think you need to learn about grouping and understand how it works

you probably want

Does this look right if I wanted to make a grid of sorts combining a person's race with ethnicity? I want to show how many people are white/hispanic or latino, white/non-hispanic or latino, etc.

select count(e.id), e.name, count(r.id), r.name
from enrollment_info_real
inner
join ethnicity e
on coalesce(enrollment_info_real.ethnicity_id,3)
= e.id
inner
join race r
on coalesce(enrollment_info_real.race_id,7)
= r.id
group
by e.id, e.name, r.id, r.name

I think I have it right:

select count(eir.id), e.name as ethnicity, r.name as race
from enrollment_info_real eir
inner
join ethnicity e
on coalesce(eir.ethnicity_id,3)
= e.id
inner
join race r
on coalesce(eir.race_id,7)
= r.id
group
by e.id, e.name, r.id, r.name

This would create the grid but now I am looking for a way to sum the columns on the bottom of the grid and the rows on the right hand side of the grid. Any ideas?

your GROUP BY is still wrong

have you been testing these queries?

what database is this?

Yes the totals all check out since I did manual queries on the database to test the results of the query I'm working on. What is wrong with the group by clause?

Is it because I had the e.id column in my group by clause?

my mistake, i thought you were counting e.id, you're actually counting eir.id

what database is this?

It's a database with the main table being a list of demographics of people signing up for a certain service. There are lots of lookup tables (race, ethnicity, etc.)

So how could I sum the bottom and right hand side of the grid? And btw, thanks for your help

"what database" means is it access? mysql? sybase? db2? informix? freebird?

i'm sorry, i don't know what you mean by "grid"

sorry, SQL Server 2000. By grid I mean:


-------------Race Race Race Total
Ethnicity 8 2 5 ?

Ethnicity 7 10 8 ?

Ethnicity 6 15 2 ?

Total ? ? ? ?


I'm trying to figure out how to get the totals.

to get totals at the end of the line, you would add another term to the SELECT list

however, in your case, this doesn't make sense, because you are only selecting one aggregate amount per line

to get a total line, in general, you would use UNION ALL and a separate query for the totals

i just don't see how your query produces a grid