Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dzabor
Posting Yak Master
138 Posts |
 Posted - 2011-10-12 : 09:23:51
|
| I am creating a temp table for my calculated fields. It keeps returnign the error that I need to use 'where exists', but I cannot find the right syntax for that.create table #TempCert(Student_ID varchar(10),[Initital Certification Date] datetime)insert into #TempCert (Student_ID, [Initital Certification Date])select Student_ID, (select Min(completion_date) as [Initital CertDate], student_idfrom Cert_Register group by student_id)from Cert_Register Thanks! |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-12 : 09:31:25
|
You're missing a join between the outer select and the subselect. But the entire subselect seems obsolete:insert into #TempCert (Student_ID, [Initital Certification Date])select Student_ID, Min(completion_date) as [Initital CertDate]from Cert_Register group by student_id - LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
 |
|
|
dzabor
Posting Yak Master
138 Posts |
Posted - 2011-10-12 : 09:49:24
|
| I was really overthinking that one. Thanks! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-10-13 : 01:39:20
|
Hehe..KISS is my favorite principle :)The "where exists" error message appears because you are not joining the inner select with anything. What you would normally do is something like this (doesn't include the where exists but this is how it's generally done):select Student_ID, [Initital CertDate] = (select Min(completion_date) from some_other_lookup_table b where a.Student_ID = b.Student_ID)from Cert_Register awhere ... - LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
|
|
Where exists