Subquery

Question

Post reply

Subquery

JimS-Indy

SSCarpal Tunnel

Points: 4514
More actions
December 16, 2010 at 9:29 am

#232106

Current Query:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
dbo.Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID
WHERE (dbo.Transactions.ReturnDate IS NULL)
Want to change it so it returns ALL Asset rows, regardless, and only returns Transaction rows where the ReturnDate is NULL. I would think it would be coded:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
(Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL)
ON dbo.Asset.AssetID = dbo.Transactions.AssetID
...but that doesn't pass the syntax checker. Help?
Jim

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Can you just remove the where clause to get all rows???

To look at your error:

Your subquery does not have an alias. You also should not use select *

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 2

JimS-Indy (12/16/2010)
Current Query:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
dbo.Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID
WHERE (dbo.Transactions.ReturnDate IS NULL)
Want to change it so it returns ALL Asset rows, regardless, and only returns Transaction rows where the ReturnDate is NULL. I would think it would be coded:
USE [AssetLending]
GO
/****** Object: View [dbo].[AssetsWithCurrentTrans] Script Date: 12/16/2010 11:24:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[AssetsWithCurrentTrans]
AS
SELECT dbo.Asset.AssetID, dbo.Asset.AssetDescription, dbo.Asset.AssetCategory, dbo.Asset.AssetCondition, dbo.Asset.AssetManufacturer, dbo.Asset.AssetModelNbr,
dbo.Asset.AssetSerialNbr, dbo.Asset.AssetTagNbr, dbo.Asset.AssetAttachment, dbo.Asset.AssetStatus, dbo.Asset.AssetDateAcquired, dbo.Asset.AssetDateRetired,
dbo.Asset.AssetLocation, dbo.AssetStatus.StatusDescription, dbo.Location.LocationDescription, dbo.Transactions.TransID, dbo.Transactions.WorkerID,
dbo.Transactions.TransDate, dbo.Transactions.ReturnDate, dbo.Transactions.Notes
FROM dbo.Asset INNER JOIN
dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN
dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN
(Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL)
ON dbo.Asset.AssetID = dbo.Transactions.AssetID
...but that doesn't pass the syntax checker. Help?

I think you have forgotten giving alias to

...........

FROM dbo.Asset INNER JOIN

dbo.AssetStatus ON dbo.Asset.AssetStatus = dbo.AssetStatus.StatusID INNER JOIN

dbo.Location ON dbo.Asset.AssetLocation = dbo.Location.LocationID LEFT OUTER JOIN (Select * from dbo.Transactions WHERE dbo.Transactions.ReturnDate IS NULL) Transactions ON dbo.Asset.AssetID = dbo.Transactions.AssetID

--Divya

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 3

JimS-Indy

SSCarpal Tunnel

Points: 4514

January 6, 2011 at 2:52 am

#1269951

Thank you, Divya. You're right!

Jim