Select help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select help

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2011-01-21 : 05:22:33

Hi friends,

I have a select statement that also selects the top price from another table should it exist.

I would like to add a check that if Prod_Price is null to not include in the select statement. I tried and Prod_price is not null in the where clause but get an error on the prod_price doesn't exist, what am I doing wrong?

Many thanks in advance

SELECT [dbo].[TB_Product].*,
(
SELECT TOP 1 Option_Price
FROM dbo.TB_Product_Option
WHERE (TB_Product_Option.Product_ID = dbo.TB_Product.Product_ID)
ORDER BY Option_Price ASC
) As Prod_Price,

FROM @TempItems T
INNER JOIN [dbo].[TB_Product] ON T.Product_ID = [dbo].[TB_Product].Product_ID
LEFT OUTER JOIN [dbo].[TB_Product_Type] ON [dbo].[TB_Product].[Product_Type] = [dbo].[TB_Product_Type].[Product_Type_ID]
LEFT OUTER JOIN [dbo].[TB_Brand] ON [dbo].[TB_Product].[Product_Brand] = [dbo].[TB_Brand].[Brand_ID]

WHERE T.ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-21 : 05:28:06

SELECT [dbo].[TB_Product].*,
(
SELECT TOP 1 Option_Price
FROM dbo.TB_Product_Option
WHERE (TB_Product_Option.Product_ID = dbo.TB_Product.Product_ID)
ORDER BY Option_Price ASC
) As Prod_Price,

FROM @TempItems T
INNER JOIN [dbo].[TB_Product] ON T.Product_ID = [dbo].[TB_Product].Product_ID
LEFT OUTER JOIN [dbo].[TB_Product_Type] ON [dbo].[TB_Product].[Product_Type] = [dbo].[TB_Product_Type].[Product_Type_ID]
LEFT OUTER JOIN [dbo].[TB_Brand] ON [dbo].[TB_Product].[Product_Brand] = [dbo].[TB_Brand].[Brand_ID]

WHERE T.ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

and
(
SELECT TOP 1 Option_Price
FROM dbo.TB_Product_Option
WHERE (TB_Product_Option.Product_ID = dbo.TB_Product.Product_ID)
ORDER BY Option_Price ASC
)
is not null

No, you're never too old to Yak'n'Roll if you're too young to die.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-21 : 05:32:24

by the way if you want you can check the below query and if the result is same to the above one, then can use it too ..

Select

P.ColumnName1,P.ColumnName2,...P.ColumnNameN,MAX(TPO.Option_Price)

FROM @TempItems T
INNER JOIN [dbo].[TB_Product] P ON T.Product_ID = P.Product_ID
Inner Join dbo.TB_Product_Option TPO on TPO.Product_ID = P.Product_ID
WHERE T.ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
Group by P.ColumnName1,P.ColumnName2,...P.ColumnNameN

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2011-01-21 : 05:32:46

oh good grief, why didn't I think of that? I feel stupid now.

Thanks webfred

Sometimes the simplest of answers stare at you straight in the face but they can be so hard to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources