Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    May 2004
    Posts
    15

    Unanswered: SELECT query combining two field ids

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    select ???
      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 ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    15
    What does coalesce do?

  4. #4
    Join Date
    May 2004
    Posts
    15
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    select e.name, r.name, count(*) as total
    ...
    group by e.name, r.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Posts
    15
    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

  7. #7
    Join Date
    May 2004
    Posts
    15
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your GROUP BY is still wrong

    have you been testing these queries?

    what database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2004
    Posts
    15
    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?

  10. #10
    Join Date
    May 2004
    Posts
    15
    Is it because I had the e.id column in my group by clause?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my mistake, i thought you were counting e.id, you're actually counting eir.id

    what database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    May 2004
    Posts
    15
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "what database" means is it access? mysql? sybase? db2? informix? freebird?

    i'm sorry, i don't know what you mean by "grid"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2004
    Posts
    15
    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.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •