Unexpected results from simple query

Question

Unexpected results from simple query

Joseph Fallon

SSCommitted

Points: 1669
More actions
July 3, 2007 at 7:03 am

#124894

Can anyone tell me what's going on here ?
I came across something like this while using a bit of SQL sent to me by
a user who wanted records deleted from a live DB.

Luckily I checked it first - here's a modified example.

My question is - why do I get 3 records when using the unqualified table name ?
I know you're supposed to use the fully qualified name but I didn't think it could act this weirdly !

-- ***************************************************
IF EXISTS (SELECT 1 FROM Sysobjects where name = 'MyTab' )
DROP TABLE MyTab
GO
CREATE TABLE MyTab
(Record_ID int,
TransactionLinkID INT)
GO
SET NOCOUNT ON
GO
INSERT INTO MyTab VALUES (1,10)
INSERT INTO MyTab VALUES (2,20)
INSERT INTO MyTab VALUES (3,30)
GO
select *
/* Change next line to "FROM dbo.MyTab" to get this to return the expected two records */
FROM MyTab
-- FROM dbo.MyTab
where record_ID IN
(
SELECT     MyTab.record_id
FROM         dbo.MyTab
WHERE     (dbo.MyTab.TransactionLinkID = 10 OR
                      dbo.MyTab.TransactionLinkID = 20 )
)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

I think the SELECT MyTab.record_id in the subquery is going to the select the record_id from the main table, thus returning all rows. Changing the subquery to SELECT dbo.MyTab.record_id will probably produce the expected result. I always use table aliases in order to avoid this sort of problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, the query without the dbo.MyTab works as expected in SQL Server 2005. I also ran it under SQL Server 2000 and got the unexpected result you reported. I then aliased MyTab (didn't add dbo.) and it returned the expected two rows. This is my guess, and others can confirm or deny it, but it looks like it returning the value of the Record_ID from the outer query instead of the subquery. I again aliased MyTab (FROM MyTab a) in the outer query and changed MyTab.Record_ID in the subquery to a.Record_ID and it returned 3 records.

When you changed FROM MyTab to FROM dbo.MyTab you actually changed the query.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 3

If I am connected to the database with a username 'Dave' and execute this statement in QA:

CREATE TABLE MyTab

(Record_ID int,

TransactionLinkID INT)

I am really creating a table named Dave.MyTab. That's a SS2K thing.

If you look in your object browser in QA, do you find two tables, one named (something like) Joseph.MyTab and one named dbo.MyTab?

There is no "i" in team, but idiot has two.