Post reply

Query Help

  • January 2, 2013 at 10:39 am

    #286746

    Hello Everyone,

    I need one query help

    create table #temp1

    (

    StudentLocalID varchar(9),

    EnrollSchoolCode Varchar(3)

    )

    create table #temp2

    (

    StudentLocalID varchar(9),

    ScheduleSchoolCode varchar(3)

    )

    insert into #temp1 values ('003682153','305')

    insert into #temp1 values('003682153','303')

    insert into #temp1 values('003744930','305')

    insert into #temp1 values('003744930','332')

    insert into #temp2 values('003682153','305')

    insert into #temp2 values('003682153','303')

    insert into #temp2 values('003744930','305')

    insert into #temp2 values('003744930','303')

    insert into #temp2 values('003744930','332')

    I need to display unmatch ScheduleSchoolCode from EnrollSchoolCode of #temp2 only

    so desired output is

    StudentLocalIDScheduleSchoolCode

    003744930303

  • January 2, 2013 at 10:41 am

    #1572982

    this should work

    Select * from #temp2

    EXCEPT

    Select * from #temp1

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • January 2, 2013 at 10:44 am

    #1572983

    Jason-299789 (1/2/2013)

    this should work

    Select * from #temp2

    EXCEPT

    Select * from #temp1

    Thanks for your help

    can you please solve another way, so i can implement that in my real logic.

    Thanks For Your Help

  • January 3, 2013 at 12:07 am

    #1573185

    Ok, I didnt realise there was any other logic.

    This is a way to do it with a Left Join

    Select t2.*

    from #temp2 t2

    Left Join #temp1 t1 on t2.StudentLocalID=t1.StudentLocalID and t2.ScheduleSchoolCode=t1.EnrollSchoolCode

    where

    t1.EnrollSchoolCode is Null

    Heres a way with a Not Exists

    Select t2.*

    from #temp2 t2

    Where

    NOT EXISTS (Select 1 from #temp1 t1

    Where t1.EnrollSchoolCode=t2.ScheduleSchoolCode

    and t1.StudentLocalID=t2.StudentLocalID)

    Either should enable you to add more logic.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • January 3, 2013 at 12:24 am

    #1573187

    this should work!!!!!

    Select * from #temp2

    EXCEPT

    Select * from #temp1

    Manik
    You cannot get to the top by sitting on your bottom.

  • January 3, 2013 at 12:36 am

    #1573188

    manikandanps (1/3/2013)

    this should work!!!!!

    Select * from #temp2

    EXCEPT

    Select * from #temp1

    I already provided that solution but the poster needs to add more logic so I've provided the additional solutions, which should allow for more logic to be provided.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • January 3, 2013 at 7:02 am

    #1573300

    Jason-299789 (1/3/2013)

    Ok, I didnt realise there was any other logic.

    This is a way to do it with a Left Join

    Select t2.*

    from #temp2 t2

    Left Join #temp1 t1 on t2.StudentLocalID=t1.StudentLocalID and t2.ScheduleSchoolCode=t1.EnrollSchoolCode

    where

    t1.EnrollSchoolCode is Null

    Heres a way with a Not Exists

    Select t2.*

    from #temp2 t2

    Where

    NOT EXISTS (Select 1 from #temp1 t1

    Where t1.EnrollSchoolCode=t2.ScheduleSchoolCode

    and t1.StudentLocalID=t2.StudentLocalID)

    Either should enable you to add more logic.

    Thank You so Much This works

    Awesome

  • January 3, 2013 at 9:01 am

    #1573369

    yogi123 (1/2/2013)

    Jason-299789 (1/2/2013)

    this should work

    Select * from #temp2

    EXCEPT

    Select * from #temp1

    Thanks for your help

    can you please solve another way, so i can implement that in my real logic.

    Thanks For Your Help

    Can you explain why this wouldn't work for you?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply