SELECT Problem - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT Problem

Author

Topic

MrCoder
Starting Member

1 Post

Posted - 2010-06-14 : 05:45:37

Hello Everyone, i'm new here.

I am a web developer. i am trying to select 3 tables.

I'm developing a, wall supports for projector, LCD TV, Plasma TV devices web site.

People can search wall supports for their own plasma tv's in this website.

Wall Supports has got 3 different dimensions

1- Width (This dimension for holes on product)
2- Height (This dimension for holes on product)
3- ProductWidth (This dimension for real width of product)

There is 2 product classes in this company.

1 - Universal Products (This Products can be use for all of Plasma Tv's.But The Condition is Plasma Tv holes width and height have to be smaller than this products holes and Plasma tv real width have to be bigger than this products real width.

For Example ;
Wall Support Properties

Width : 200
Height : 200
RealWidth : 450

3 Data incoming from outside

IntWidth (This is Width For Holes)
IntHeight (This is Height For Holes)
IntRealWidth (This is For RealWidth)

IntWidth = 100 (Smaller Than Product This is Ok)
IntHeight = 100 (Smaller Than Product This is Ok)
IntRealWidth = 500 (Bigger Than Product This is Ok)

2 - Named Products ( This Products can be use for all plasma tv's. But the condition is Plasma tv holes width and height have to be equal to this product. Real width does not matter for this Product category.

Width : 200
Height : 200
RealWidth : 450

3 Data incoming from outside

IntWidth (This is Width For Holes)
IntHeight (This is Height For Holes)
IntRealWidth (This is For RealWidth)

IntWidth = 200 (Equal to Product This is Ok)
IntHeight = 200 (Equal to Product This is Ok)
IntRealWidth = 500 (This is not neccessary for this query)

Important Fields In Tables

Products : (ProductID)
NamedDimensions :(ProductWidth,ProductHeight,ProductID)
UniversalDimensions :(ProductWidth, ProductHeight,ProductID,RealWidth)

in Named and Universal dimensions tables, fields "ProductID" is my key

My Query :

SELECT * FROM Products
INNER JOIN NamedDimensions ON Products.ProductID = NamedDimensions.ProductID
INNER JOIN UniversalDimensions ON Products.ProductID = UniversalDimensions.ProductID
WHERE (NamedDimensions.Width = IntWidth) AND (NamedDimensions.Height = IntHeight)
OR (UniversalDimensions.Width > IntWidth) AND (UniversalDimensions.Height > IntHeight) AND (UniversalDimensions.RealWidth > IntRealWidth)

PS : IntWidth,IntHeight and IntRealWidth's representational in this query. that dimensions is taking real numbers.

This query does not work in program. There is no field in database returned.

But, when i remove WHERE row database returning NamedProducts. When i Remove FROM row database returning all Products. AND (This is important: When is Just Use Named Products table query is working properly. but When i use Universal Dimensions table database is givings no product records.

Can you guys write to me a right query for this problem ?

Finally : i am sorry for my english, i know my english is bad. thanks for your passions.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-14 : 06:11:50

Getting your brackets right when using OR is vital, if the following doesn't return anything, then it may be your data.


SELECT P.* 
FROM Products P
INNER JOIN NamedDimensions ND
ON P.ProductID = ND.ProductID
INNER JOIN UniversalDimensions UD
ON P.ProductID = UD.ProductID
WHERE 
(
ND.Width = IntWidth 
AND ND.Height = IntHeight
)
OR 
(
UD.Width > IntWidth
AND UD.Height > IntHeight 
AND UD.RealWidth > IntRealWidth
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources