Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 10:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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

StudentLocalID	ScheduleSchoolCode
003744930 303
Post #1401962
Posted Wednesday, January 2, 2013 10:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
this should work

Select * from #temp2
EXCEPT
Select * from #temp1



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1401964
Posted Wednesday, January 2, 2013 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
Post #1401966
Posted Thursday, January 3, 2013 12:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1402204
Posted Thursday, January 3, 2013 12:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:00 AM
Points: 252, Visits: 225
this should work!!!!!
Select * from #temp2
EXCEPT
Select * from #temp1


Manik
You cannot get to the top by sitting on your bottom.
Post #1402210
Posted Thursday, January 3, 2013 12:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1402211
Posted Thursday, January 3, 2013 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
Post #1402348
Posted Thursday, January 3, 2013 9:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 23,009, Visits: 31,510
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1402428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse