June 19, 2008 at 10:24 pm
The code below works fine; however, I need to be able to use this without the @HomeID paramenter.
DECLARE @HomeID INT
SET @HomeID = 2
SELECT utbHome.*, finfo.FileID, finfo.Name as 'FileName'
FROM utbHome LEFT JOIN
utbAmenities ON utbHome.HomeID = utbAmenities.HomeID LEFT JOIN
(SELECT HomeID, utbFile.FileID, utbFile.[Name] FROM utbFile INNER JOIN utbHomeUtbFile ON
utbHomeUtbFile.FileID = utbFile.FileID WHERE utbHomeUtbFile.FileID IN
(SELECT utbHomeUtbFile.FileID FROM utbHomeUtbFile WHERE utbHomeUtbFile.HomeID = @HomeID
AND utbHomeUtbFile.IsDefault = 1)) finfo
ON finfo.HomeID = utbHome.HomeID
WHERE utbHome.HomeID IN
(SELECT HomeID
FROM dbo.utbInventory)
I need this (SELECT utbHomeUtbFile.FileID FROM utbHomeUtbFile WHERE utbHomeUtbFile.HomeID = @HomeID <------ to be the in the same list as the WHERE Condition
--> WHERE utbHome.HomeID IN
(SELECT HomeID
FROM dbo.utbInventory)
how do I map the rows correctly?
Erik
Dam again!
June 20, 2008 at 8:31 am
This is how I would write the original query:
DECLARE @HomeID INT
SET @HomeID = 2
SELECT utbHome.*, finfo.FileID, finfo.Name as 'FileName'
FROM utbHome
LEFT JOIN utbAmenities
ON utbHome.HomeID = utbAmenities.HomeID
LEFT JOIN
(SELECT HomeID, utbFile.FileID, utbFile.[Name]
FROM utbFile
INNER JOIN utbHomeUtbFile
ON utbHomeUtbFile.FileID = utbFile.FileID
WHERE utbHomeUtbFile.FileID IN
(SELECT utbHomeUtbFile.FileID
FROM utbHomeUtbFile
WHERE utbHomeUtbFile.IsDefault = 1)
AND HomeID = @HomeID) finfo
ON finfo.HomeID = utbHome.HomeID
WHERE utbHome.HomeID = @HomeID
This is how I would write it without the @HomeID parameter:
SELECT utbHome.*, finfo.FileID, finfo.Name as 'FileName'
FROM utbHome
LEFT JOIN utbAmenities
ON utbHome.HomeID = utbAmenities.HomeID
LEFT JOIN
(SELECT HomeID, utbFile.FileID, utbFile.[Name]
FROM utbFile
INNER JOIN utbHomeUtbFile
ON utbHomeUtbFile.FileID = utbFile.FileID
WHERE utbHomeUtbFile.FileID IN
(SELECT utbHomeUtbFile.FileID
FROM utbHomeUtbFile
WHERE utbHomeUtbFile.IsDefault = 1)) finfo
ON finfo.HomeID = utbHome.HomeID
WHERE utbHome.HomeID IN
(SELECT HomeID
FROM dbo.utbInventory)
The second has no input parameters and will thus probably be a large data set.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 8:44 am
This is perfect !
Thank you very much for your help !
Erik
Dam again!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply