I have a problem where the user has set his preferences in a table.
The table has more than 5 columns. Now I want to search the other view with condition values that are in the
@colour = Select Top 1 Colour from preferences;
You have to declare the variables, then you can use them to assign the values in the
SELECT and use them later:
-- declare the variables DECLARE @min_price decimal(8,2) DECLARE @max_price decimal(8,2) DECLARE @color varchar(20) -- assign the values into the variables SELECT @color = Colour, @min_price = MIN_PRICE, @max_price = MAX_PRICE FROM Preference WHERE preference_id = (parameter of prefrence id) -- Now you get all 3 values of Colour, Min Price, Max Price, -- then these 3 values can be used for your query SELECT * FROM products WHERE colour = @color AND price BETWEEN min_price AND @max_price
Or you can make a single query if these 3 variables are not used any other place:
SELECT * FROM products p INNER JOIN ( SELECT Colour, MIN_PRICE, MAX_PRICE FROM Preference WHERE preference_id = (parameter of prefrence id) ) pre ON (p.colour = pre.Colour AND p.price BETWEEN pre.MIN_PRICE AND pre.MAX_PRICE)