Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    2

    Unanswered: Need help with query please

    I want to run a query that will pull all city administrators names, if there is not a city administrator for a city it should pull the city clerks name for that city. A city can have both.

    I have a field named PC for position code, this field can only contain one position code. (i.e. "1005" for Mayor; "1006" for City Clerk; etc.)

    When I state:

    PC="1006' or PC="1010" I will get both for a city if the city contains both the city clerk and city administrator. I don't care about the city clerk if there is a city administrator.

    Can someone give me a suggestion here please.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select administratorname
      from citypositions
     where pc = '1010'
    union all  
    select administratorname
      from citypositions as T
     where pc = '1006'
       and not exists
           ( select 937
               from citypositions
              where city = T'city
                and pc = '1010' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    2

    I get an error message

    when I type that in. A syntax error message.

    I am a novice here too. What does city positions as T mean?

    and Select 937? Where does that come from?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aw, shoot, a typo -- please replace T'city with T.city

    T is a table alias

    937 could be anything -- what matters with an EXISTS subquery is whether the subquery returns any rows or not
    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
  •