i dont understand

Question

i dont understand

sqlpro

Hall of Fame

Points: 3859
More actions
December 18, 2003 at 6:50 pm

#81750

Dear friends
i am having wrong results with following query.because of variable i'm using.
as u can see i am using @x in 'where ' condition.(i.e Assignment.fk_staffid LIKE @x)
when i use @x query missing out 2 records which r in task table but not in assignment table.but if i hard code above stmt like following
Assignment.fk_staffid LIKE '%'
then it gives exact result.
can u guys shed some light on it please.
The actual sql stmt is
declare @x varchar(5)
set @x='%'
SELECT Task.Taskid,Task.Entrydt AS entrydt, Task.Taskname,Task.TaskPriority,
'Completed'= case Task.complete when 0 then 'No' else 'Yes' end,
Task.Descr, Task.Comments,
Category.Catname AS Category, Task.fk_catid, Task.Status,
Taskgroup.fk_staffid, Staff.Staffname, Test.testid,
'test1'=case when Test.test1=1 then 1 else 0 end,
'test2'=case when Test.test2=1 then 1 else 0 end,
Test.tdate1, Test.tdate2
FROM Task INNER JOIN
Category ON Task.fk_catid = Category.Catid INNER JOIN
Taskgroup ON Task.Taskid = Taskgroup.fk_taskid INNER JOIN
Staff ON Taskgroup.fk_staffid = Staff.StaffId LEFT OUTER JOIN
Assignment ON Task.Taskid = Assignment.fk_taskid LEFT OUTER JOIN
Test ON Task.Taskid = Test.fk_taskid
WHERE (Task.rowactive = 0) AND (Task.fk_catid LIKE '%') AND
(Task.Descr LIKE '%' OR Task.Descr LIKE ' %') AND
(Taskgroup.fk_staffid LIKE '%') AND (Taskgroup.taskowner = '1') and
(Assignment.fk_staffid LIKE @x)
GROUP BY Task.Taskid, Task.Entrydt, Task.Taskname, Task.TaskPriority, Task.complete, Task.Descr, Task.Comments, Task.fk_catid, Category.Catname,
Task.Status, Taskgroup.fk_taskid, Taskgroup.fk_staffid, Staff.Staffname, Test.testid, Test.test1, Test.test2, Test.tdate1, Test.tdate2
ORDER BY Task.Entrydt DESC, Task.Taskid, Test.tdate1 DESC

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

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

gljjr SSChampion Points: 10080 More actions · Answer 1

I think the problem is that you are using the Like statement on the outer table in the where clause rather than the join clause. This will in effect cause you to have invalid results when doing outer joins! Try the following instead.



declare @x varchar(5)

set @x='%'



SELECT Task.Taskid

    ,Task.Entrydt AS entrydt

    , Task.Taskname

    , Task.TaskPriority

    , 'Completed'= case Task.complete when 0 then 'No' else 'Yes' end

    , Task.Descr

    , Task.Comments

    , Category.Catname AS Category

    , Task.fk_catid, Task.Status

    , Taskgroup.fk_staffid

    , Staff.Staffname

    , Test.testid

    , 'test1'=case when Test.test1=1 then 1 else 0 end

    , 'test2'=case when Test.test2=1 then 1 else 0 end

    , Test.tdate1

    , Test.tdate2

FROM Task 

    INNER JOIN Category ON Task.fk_catid = Category.Catid 

    INNER JOIN Taskgroup ON Task.Taskid = Taskgroup.fk_taskid 

        INNER JOIN Staff ON Taskgroup.fk_staffid = Staff.StaffId 

    LEFT OUTER JOIN Assignment ON Task.Taskid = Assignment.fk_taskid  AND (Assignment.fk_staffid LIKE @x)

    LEFT OUTER JOIN Test ON Task.Taskid = Test.fk_taskid

WHERE (Task.rowactive = 0) 

    AND (Task.fk_catid LIKE '%') 

    AND (Task.Descr LIKE '%' OR Task.Descr LIKE ' %') 

    AND (Taskgroup.fk_staffid LIKE '%') AND (Taskgroup.taskowner = '1')

GROUP BY Task.Taskid

    , Task.Entrydt

    , Task.Taskname

    , Task.TaskPriority

    , Task.complete

    , Task.Descr

    , Task.Comments

    , Task.fk_catid

    , Category.Catname

    , Task.Status

    , Taskgroup.fk_taskid

    , Taskgroup.fk_staffid

    , Staff.Staffname

    , Test.testid

    , Test.test1

    , Test.test2

    , Test.tdate1

    , Test.tdate2

ORDER BY Task.Entrydt DESC, Task.Taskid, Test.tdate1 DESC

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Dear Gary Johnson

you r wonderful mate.

i've been struggling for 2 hours.

Your suggestion worked. 🙂

Thank you so much

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

HI

i'm back again.it worked fine when i pass only % but if i pass some staffid(i mean some value other than %) it bring wrong results.

for example if i want to see some particular staff assignments it will bringout assignments of staff i want 2 see and as well as other assignments of different people .

how could i filter out others?

gljjr SSChampion Points: 10080 More actions · Answer 4

More than likely the problem lies in the outer join. Without knowing more about your data it is really hard for me to know how to write the query. Also, What is the reason for the "Test" table having an outer join in this query?

Also notice that the Assignment table is joined to the doesn't contain a join to the Staff table. So on the left join for Assignment if you add

"AND Staff.Staffid = Assignment.fk_StaffID"

it should do the trick.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

Thanks for quick reply Gary

here Task primary key table for Assignment table.its 1 to many relationship.

on a single Task there may b more than 1 person working on it.so when i pass staffid

it should reurn assignments of that particular staff .if no staffid passed it should return all staff info whoever working on that particular task.

for Test table Task table is parent.Here again 1 to many relationship.there can b testing details or they may b no testing details .

plz ask me if u need more info.

i tried what u suggestion in ur last post but did not work.

Thanks

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

Hi gary

i think i understand what the problem is.

i have 2 tasks with no assignments on it.

so when i pass staffid the query returns those 2 tasks too.

is there any way when i pass staffid and there r no assignments it should return those 2 tasks.but if i pass no staffid then it should return all tasks+those 2 tasks.

how can i do that.

cheers

gljjr SSChampion Points: 10080 More actions · Answer 7

You might look into the ISNULL and COALESCE functions to see if they would do what you want. IE:

Assignment.fk_staffid LIKE (ISNULL(@x,Assignment.fk_staffid)

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

sqlpro Hall of Fame Points: 3859 More actions · Answer 8

Thanks Gary

Actually left outer join is the key here.i

think i need basically 2 queries.when staffid passed it will b inner join on Task.if no staffid passed it will b left outer join on Task.

i am wondering if i could achieve this in 1 query.

thanks anyway for ur valuable time