Saturday, February 25, 2012

query not working

Hi
look at the queries
1.
Examination_timetable consists of rows like this
ClassId ExamDate SubId Invigilation
CLD00001 3/12/2006 SUB00001 STA00001,STA00002
select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001'
then i got following correct answer which is correct.
invigilation
STA00001,STA00002
2.
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
('STA00001','STA00002')
then i didnt get any rows which is also correct, since except
STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
teaching staff's id's).
3.
now i joine teh both 1st and 2nd queries like this
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
(select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001')
then i'm getting result like this, whics is wrong result
StaffId
STA00001
STA00002
it shouldn't display any rows since no rows matches the given condition.
why it is displaying wrong answer when i mixed 2 separates queries in to
single query, where both individually giving correct answer?
hope you got my query.
can any one tell me where i went wrong?
thanx in advance
yoshithaHow about
select staffid from staff_details INNER JOIN faculty_type
ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
Examination_timetable ON staff_details.staffid NOT LIKE '%' +
Examination_timetable.Invigilation + '%'
where facultytype='teaching'
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||Your usage of IN is wrong. When you get the "invigilationID", it is a comma
separated string. However, for IN to search through this, you cannot
substitute this string directly for the IN clause, but rather, you need to
do it dynamically. Here is an example that shows how it does not work:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN 'Test1' IN (@.testString) THEN 1 ELSE 0 END
=====
When you execute the above snippet, you will always get 0 (although Test1 is
present inside the string). To get around this problem, you can do the
following
(1) Use dynamic SQL to build your query as shown:
=====
DECLARE @.testString VARCHAR(100)
DECLARE @.strSQL VARCHAR(8000)
SET @.testString = '''Test1'', ''Test2'''
SET @.strSQL = 'SELECT CASE WHEN ''Test1'' IN (' + @.testString + ') THEN 1
ELSE 0 END'
EXEC (@.strSQL)
=====
(2) Use CHARINDEX as shown:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN CHARINDEX('Test1', @.testString) > 0 THEN 1 ELSE 0 END
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||still it is displaying both ids.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> How about
> select staffid from staff_details INNER JOIN faculty_type
> ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
> Examination_timetable ON staff_details.staffid NOT LIKE '%' +
> Examination_timetable.Invigilation + '%'
> where facultytype='teaching'
>
>
> "yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
> news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||Well, can you post DDL+ sample data + expected result for both tables?
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:e9CoRrHMGHA.2992@.tk2msftngp13.phx.gbl...
> still it is displaying both ids.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

No comments:

Post a Comment