Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Location
    Chennai, India
    Posts
    15

    Unanswered: retrieve all names that don't have a year (was "Query to be written to fetch ....")

    Having two tables.

    1. ID Year
    ----------
    1 1990
    2 1991
    3 1992



    2. Name Year
    --------------
    ABC 1
    XYZ 2
    123 3


    By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.

    For example, if pass 1990, then the result set should be 'XYZ' and '123'

    Thanks in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    This should work ...

    create table q_tmp
    (
    id smallint,
    year varchar(4)
    )

    create table q_tmp2
    (
    name varchar(3),
    year smallint
    )


    insert into q_tmp
    select 1, '1990'
    union all
    select 2, '1991'
    union all
    select 3, '1993'

    insert into q_tmp2
    select 'abc', 1
    union all
    select 'xyz', 2
    union all
    select '123', 3



    select q_tmp2.name from q_tmp2, q_tmp where q_tmp.year <> '1990' and q_tmp2.year = q_tmp.id


    Depending on what you mean by "passing year as a argument" this should suffice. If you're looking for a procedure with a parameter, then you should re-write my select statement into a procedure using a variable instead of the year.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are at least two ways to do this:
    Code:
    select Name
      from table2
     where not exists
           ( select Year
               from table1
              where ID = table2.Year
                and Year = 1990 )  
                
    select Name
      from table2
    left outer
      join table1  
        on table2.Year = table1.ID
       and table1.Year = 1990
     where table1.ID is null
    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
  •