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

Try changing:
(select isnull(ethnicity_id, 3), isnull(race_id, 7), count(eir.id) as total
from enrollment_info_real eir)

to

(select isnull(ethnicity_id, 3), isnull(race_id, 7), count(eir.id) as total
from enrollment_info_real) eir

That way eir is the alias for the subquery.

Brian

HI,

When u use a derived table to join, always an alias name is required.
it works but better to use Joins for all the tables instead of using derived tables. You don't have to use ISNULL in this case. Just u can use "Where T1.Col1 IS NOT NULL".

For example U have Table1 with col1,col2,col3
Table2 with Col1,col2,col3 and u want to display col2 from Table1 and col3 from table2 and u don't want the table2 to return null values.


SELECT T1.COL2,T2.COL3 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON
T1.COL1=T2.COL1
WHERE T2.COL2 IS NOT NULL


If u must a subquery as derived table, u can use IS NOT NULL any where. U can also use "COL2 <> NULL" provided that ANSI_NULLS is off.

Hope this helps.

B. Anant