Subquery bug

[GJP55]

I am wondering if this is a known bug in SQL 2000 or is it a bug at all ??

use northwind
select * from Orders
where orderid in (select shipvia from [Order details])

The subquery field shipvia doesnt exist in the [Order Details] table but the full query still runs but returns no records.

I noticed this in a query I was running and for the particular tables that I was running this on, the complete query ran and just ignored the In operator clause and returned records.

It seems that as long as the field in the subquery exists in the initial query, it will run.

Bill, can I have some of you billions for spotting this or am i just being dense ?

Thanks

 

[Jamfool]

This is not a bug, you need to take a look out how to do joins correctly. the join you have: orderid - shipvia... would merely return all those orders where orderid matched shipvia.

SELECT     dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount, 
                      dbo.Orders.ShipVia
FROM         dbo.[Order Details] INNER JOIN
                      dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID

 

[GJP55]

I think my point was that shipvia was not a field in Order Detail but it still parsed and ran the query. I dont need to brush up on my joins , i just sometimes use the In operator when running queries where it suits.

 

[Jamfool]

Syntax is correct, columns and tables exist, so it parses.

--where you prefix:
use northwind
select * from Orders
where orderid in (select [Order details].ShipVia from [Order details])

--Server: Msg 207, Level 16, State 3, Line 2
--Invalid column name 'shipvia'.

begin transaction 
GO
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Orders_Shippers]
select count(*) as [original] from Orders
where orderid in (select ShipVia from [Order details])
GO
update [Orders] set ShipVia = orderid
GO
select count(*) as [orderid set to = shipvia] from Orders
where orderid = shipvia
GO
delete from [Order details]
select count(*) as Zero_RowsInOrderDetails from Orders
where orderid in (select shipvia from [Order details])
GO
insert into [Order details] values (10248,11,14.0000,12,0.0)
GO
select count(*) as One_RowInOrderDetails from Orders
where orderid in (select shipvia from [Order details])
GO
rollback transaction