Selecting data from various fields in a table

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

Other Forums

Other Topics

Selecting data from various fields in a table

Author

Topic

centralcan
Starting Member

4 Posts

Posted - 2004-07-22 : 17:10:45

I have ONE TABLE;

Selected Columns In table
Dimension_Length
Dimension_Height
Dimension_Span

The variables I wish to have returned in each one are as follows;

For Dimension_Length; it will have varLengthMin and varLengthMax
For Dimension_Height; it will have varHeightMin and varHeightMax
For Dimension_Span; it will have varSpanMin and varSpanMax

Each VAR variable has a integer value. I want to be able to have a range (BETWEEN STATEMENT) for the minimum and maximum for each table columns. Each table columns must be within the search range to be displayed from the SQL statement.

I’ve tried INTERSECT in SQL, I’ve tried sub queries. I have so far had no luck. Im programming this SQL in a ColdFusion v6.1 environment.

If anyone could help I would for sure appreciate it.

Take care

Robin.
robin@centralcan.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 17:12:10

Please show us examples of the data and what the expected result set would be.

Tara

centralcan
Starting Member

4 Posts

Posted - 2004-07-22 : 17:27:27

Hey,

at this url; it will show you the idea that im working with. hopefully it makes sense.

http://centralcan.com/misc/issue/

also, all three search for length/height/span, all the results displayed must fall in the range that is passed to all three columns.

Robin.

quote:
Originally posted by tduggan

Please show us examples of the data and what the expected result set would be.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 17:32:13

So is this an Access query question? I'm not familiar with INTERSECT, but I guess it would be equivalent to UNION in MS SQL Server.

Here's the Access forum in case this is an Access question:
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

I suspect you are looking for something like this (multiple conditions in the WHERE clause):

SELECT Column1, Column2, Column3, Column4,...
FROM Table1
WHERE Column1 BETWEEN @Var1 AND @Var2
AND Column2 BETWEEN @Var1 AND @Var2
AND Column3 BETWEEN @Var1 AND @Var2

Tara

centralcan
Starting Member

4 Posts

Posted - 2004-07-23 : 07:41:55

Hello,
Thanks for the response.

i tried applying that chunk of code, and I returned an error;

http://centralcan.com/misc/issue2/

I’ve been working on this issue for a few days now, and its probably something silly that I’m not seeing.

Also I don’t believe it to be an issue with the access database. Could ColdFusion refuse the use of selected SQL expressions?

Thanks

Take Care

Robin.
robin@centralcan.com

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 08:43:24

Get rid of the "@" signs. Tara was telling you how to do it with SQL variables (who's names start with "@") but you are using dynamic SQL, so you can just put the parameters straight into the SQL:

SELECT Column1, Column2, Column3, Column4,...
FROM #URL.varTable#
WHERE Dimensions_Height BETWEEN '#URL.varHeightMin#' AND '#URL.varHeightMax#'
AND ...

Kristen

centralcan
Starting Member

4 Posts

Posted - 2004-07-23 : 09:10:17

Hey everyone,

Thanks so much for helping me out. I have the sql statement fully functioning. Silly mistakes on my part.

http://centralcan.com/misc/solution/

Thanks Again.

Robin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources