Help me a SQL sentence£¡£¡£¡

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.

All Forums

Other Forums

Other Topics

Help me a SQL sentence£¡£¡£¡

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-26 : 08:54:58

li writes "--//please help me a school boy require^¡¡

DECLARE @condition varchar

--//create a temp table
--//´´½¨ÁÙÊ±±í
create table #Classes_Info(
classSpecialty varchar(50),
courseID char(6),
courseName varchar(50),
coursePeriod int,
courseCredit int,
courseKind int,
courseDescribe varchar(50)
)

--begin
--//<summary>:I want to the selet result insert into the temp
--//table and then select I need from the temp table

insert into #Classes_Info

select distinct ClassesInfo.classSpecialty as '×¨ÒµÃû',Courses.courseID as '¿Î³Ì±àºÅ',Courses.courseName as '¿Î³ÌÃû³Æ',Courses.coursePeriod as '¿Î³ÌÑ§Ê±',Courses.courseCredit as '¿Î³ÌÑ§·Ö',Courses.courseKind(

case Courses.courseKind

WHEN 0 THEN '¹«¹²¿Î'

WHEN 1 THEN '×¨Òµ¿Î'

WHEN 2 THEN 'Ñ¡ÐÞ¿Î'

END) as '¿Î³ÌÀàÐÍ',Courses.courseDescribe as '¿Î³ÌÃèÊö'

from Courses,ClassesInfo

where not exists(select distinct studentCourseID

from StudentCoursesInfo

where not exists(select distinct studentID

from StudentsInfo

where studentClassID=classID))

order by classSpecialty

--END

--//select I need from the temp table
--//´ÓÁÙÊ±±íÖÐ¶ÁÈ¡Ïà¹ØÊý¾Ý

select *

from #Classes_Info

where classSpecialty like'%"+condition+"%' or courseID like '%"+condition+"%' or courseName like '%"+condition+"%' or coursePeriod like '%"+condition+"%' or courseCredit like '%"+condition+"%' or courseKind like '%"+condition+"%' or courseDescribe like '%"+condition+"%'

order by classSpecialty

drop table #Classes_Info"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 09:38:22

1. As you are inserting into the temp table, the column name alias like '×¨ÒµÃû' etc are not required at all.


select distinct ClassesInfo.classSpecialty as '×¨ÒµÃû', Courses.courseID as '¿Î³Ì±àºÅ', Courses.courseName as '¿Î³ÌÃû³Æ', 
       Courses.coursePeriod as '¿Î³ÌÑ§Ê±', Courses.courseCredit as '¿Î³ÌÑ§·Ö', -- Missing comma here
       Courses.courseKind,

2. The number of columns does not match of the result set does not matched number of columns in the temp table. The temp table does not have a column for '¿Î³ÌÀàÐÍ'.

3. You need to specify the join condition between the 2 table Courses and ClassInfo


select distinct  . .  .
from   Courses c inner join ClassesInfo ci
on     c.some_columne = ci.some_column
where  . . . 

4. The where statement is also wrong. Think this is what you want.
[code]
where studentCourseID not in (select distinct studentCourseID from StudentCoursesInfo
                                 where not exists(select distinct studentID from StudentsInfo where studentClassID = classID))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources