Better Way To Write This Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Better Way To Write This Query

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2009-01-21 : 15:55:04

Hi everyone. The query at the end works. But I know there is a better way to write this. I didn't design the tables just recreating for you to see. Is this the best way to write it?
Thanks,
TallOne

CREATE TABLE [dbo].[Test_A]
(
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[UserID] [numeric](18, 0) NOT NULL,
[ItemNumber] [numeric](18, 0) NOT NULL,
[COID] [numeric](18, 0) NOT NULL
)
GO
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('1','001','1')
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('2','002','1')
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('3','003','2')
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('4','004','2')
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('1','005','1')
INSERT INTO Test_A(UserID,ItemNumber,COID)VALUES('1','006','1')

CREATE TABLE [dbo].[Test_B]
(
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[UserID] [numeric](18, 0) NOT NULL,
[LocationID] [numeric](18, 0) NULL
)
GO
INSERT INTO Test_B(UserID,LocationID)VALUES('1','101')
INSERT INTO Test_B(UserID,LocationID)VALUES('1','102')
INSERT INTO Test_B(UserID,LocationID)VALUES('2','102')
INSERT INTO Test_B(UserID,LocationID)VALUES('3','103')

CREATE TABLE [dbo].[Test_C]
(
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ItemNumber] [numeric](18, 0) NOT NULL,
[LocationID] [numeric](18, 0) NULL
)
GO
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('001','101')
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('002','101')
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('003','101')
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('004','102')
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('005','101')
INSERT INTO Test_C(ItemNumber,LocationID)VALUES('006','103')

DECLARE @COID AS NUMERIC
DECLARE @UserID AS NUMERIC

SET @COID = 1
SET @UserID = 1

Select
a.UserID,
a.itemnumber
from
Test_A a
WHERE
a.COID = @COID
AND @UserID IN
(
SELECT UserID FROM Test_B WHERE LocationID =
ISNULL((SELECT TOP 1 LocationID FROM Test_C WHERE ItemNumber = a.ItemNumber),0)
)
--DROP TABLE [dbo].[Test_A]
--DROP TABLE [dbo].[Test_B]
--DROP TABLE [dbo].[Test_C]

TallOne
Starting Member

49 Posts

Posted - 2009-01-21 : 16:15:58

Dang, after more testing my query doesn't work! I just realized that the param @UserID should be default 0 or null and not used in the where clause when default value. :(

TallOne
Starting Member

49 Posts

Posted - 2009-01-21 : 17:06:10

I've created a separate sp for this and now @UserID will always be > 0. I was just hoping to find a better way to write it. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources