Link to home
Start Free TrialLog in
Avatar of webdork
webdork

asked on

Complex SQL Statement

A gem website in classic asp.

I want to submit a color search string to a SQL Server database. Multiple values can be submitted.

The viewer is presented with a form containing a series of checkboxes: The underscores below represents the form checkbox. The viewer can select more than one checkbox.
_red
_redorange
_orange
_orangeyellow
_yellow
_blue
_bluegreen
_green

etc..

The products table in the database has a color column although I've not decided how I want to populate it.
The product can be more than one color: blue, bluegreen, green etc..

Not sure If I should use a linkage table or a sql IN statment.

Any help and/or ideas welcome.
Avatar of Pratima
Pratima
Flag of India image

you can us IN statement like this

Select * from Product
where color in ( 'blue','green','red')

is this you want or somting else ?
Avatar of webdork
webdork

ASKER

is color the column name?
Avatar of webdork

ASKER

I dont think that is quite what I need.

The color column in the products table can look like this:

ID|Name|Color|Price
12|Tanzanite|Blue,Purple,Yellow|100
13|Tsavorite|Green,BlueGreen,Pink|150

The search string could be: Blue,BlueGreen,White,Yellow in which case both product 12 and product 13 would be returned.
Since your data is not normalized you will have to write a dynamic query that contains several unions:

SELECT * FROM Products WHERE Color LIKE '%Blue%'
UNION
SELECT * FROM Products WHERE Color LIKE '%BlueGreen%'
UINION
SELECT * FROM Products WHERE Color LIKE '%White%'
UNION
SELECT * FROM Products WHERE Color LIKE '%Yellow%'
Avatar of webdork

ASKER

Is there a better way to do it? Linkage table?
If you have a finite number of possible base colours - I would consider using a bitwise column in conjunction with a lookup table for the color values.

The internet is abundant with articles on how to do this.

Mine is at:

http://mattsql.wordpress.com/2012/05/10/working-with-bitwise-data-in-sql-server/
Can you show all possible combinations? You can use RGB colors and encode each gem's color. Then you can try to find nearest available color. There are many possible algorithms:
http://stackoverflow.com/questions/1767941/objective-c-find-closest-color-rgb-match
http://stackoverflow.com/questions/1720528/what-is-the-best-algorithm-for-finding-the-closest-color-in-an-array-to-another
http://shallowsky.com/colormatch/index.php
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you are unable to separate to do what fyed suggests, you can create a function that takes a color and return true or false if it finds that color in a string. Then you can create a stored procedure or a view that links your product table an the view.

SELECT * FROM Products
WHERE fnColorContains([Color], colorsselectedAsSomeDelinitedString)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of webdork

ASKER

Color possibilities are finite. Not final yet but probably 10-20 colors only.

Yes can change table structure
So then please do so, I've shown you a way to query your table in that case:


set @search = 'Blue,BlueGreen,White,Yellow'

then you can do the following in SQL

select
from Products a
inner join ProductColor b on a.ID = b.ID
where ',' + @Search + ',' like '%,' + b.Color + ',%'
Avatar of webdork

ASKER

I'm getting a single product return where I should get more

set @search = 'Blue,BlueGreen,White,Yellow'
select *
from Products a
inner join ProductColor b on a.ProductID = b.PID
where ',' + @Search + ',' like '%,' + b.ColorName + ',%'
Avatar of webdork

ASKER

Wait, Im getting the correct return now.

ProductColor Table structure. Do I need the identity column?

ColorID|ColorName|PID
1|Red|22731
2|Orange|22731
3|Blue|22760
Avatar of webdork

ASKER

Thanks for detailed solution.
glad to help.  Believe ralmada provided the most work on this and am not averse to you reallocating points as such.
I'm cool with this allocation. You fyed suggested the table structure change in first place, and I think that was the key here. Have a nice day :)