October 2, 2003 at 3:39 am
Hello Im womdering if any one can help me see the error in the following situation.
Im trying to see the fields in one table
with no matching record in another.
the attached script correctly returns the value 3 when run on its own but if the
select * and closing ')' are uncommented the query returns all rows.
(Apologies if this is obvious to everyone else)
CREATE TABLE [dbo].[xComp] (
[Rowid] [int] IDENTITY (1, 1) NOT NULL ,
[CompName] [char] (10) NOT NULL ,
[TaskId] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[xTask] (
[RowId] [int] IDENTITY (1, 1) NOT NULL ,
[TaskName] [char] (10) NULL
) ON [PRIMARY]
insert into xComp (CompName,TaskID)
Values ('Andy',1)
insert into xComp (CompName,TaskID)
Values ('Brian',2)
insert into xComp (CompName,TaskID)
Values ('Chris',3)
insert into xTask (TaskName)
Values ('AndyTask')
insert into xTask (TaskName)
Values ('BrianTask')
--select * from xcomp where RowId in (
SELECT xComp.RowId
FROM dbo.xComp LEFT OUTER JOIN
dbo.xTask ON dbo.xComp.TaskID = dbo.xTask.RowId
WHERE (dbo.xTask.RowId IS NULL)
--)
October 2, 2003 at 3:56 am
You're referencing the same table twice in the statement without distinguishing between the two instances, so they are treated as one instance. Use aliases:
SELECT *
FROM dbo.xComp
WHERE RowId in (
SELECT x.RowId
FROM dbo.xComp x LEFT OUTER JOIN dbo.xTask t ON x.TaskID = t.RowId
WHERE t.RowId IS NULL)
--Jonathan
--Jonathan
October 2, 2003 at 4:11 am
Thanks Jonathan.
October 3, 2003 at 12:49 am
Why using left join? Do it more simple:
SELECT *
FROM dbo.xComp d
where not exists(
select * from dbo.xTask
where rowid=d.taskid)
October 3, 2003 at 8:47 am
Actually Chris you all ready had the solution...
With just a slight change to your original subquery, you have what you were looking for. In fact, the solution presented by Jonathan adds an extra step into the query that is not necessary.
Here is the solution you already had...
SELECT xComp.*
FROM dbo.xComp
LEFT OUTER JOIN dbo.xTask
ON dbo.xComp.TaskID = dbo.xTask.RowId
WHERE (dbo.xTask.RowId IS NULL)
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply