Conditional Joins - 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)

Conditional Joins

Author

Topic

Odhran
Starting Member

1 Post

Posted - 2010-11-15 : 11:11:52

Hi

I'm trying to do something without having to resort to dynamic SQL strings.

I'm basically trying to perform a join where the operator of the ON statement comes from a column in the query.

I'll try and outline a simplified version of my situation.

I have two tables:

filters:
filterID int
questionID int
operator varchar
constant float

answers:
answerID int
questionID int
itemID int
answer float

What I am trying to achieve is to pull back a list of itemIDs where answer matches the filters stored in the filter table.

e.g.
filterID questionID operator constant
1 13 < 50
2 14 = 45

answerID questionID itemID answer
1 13 1 10
2 14 1 45
3 13 2 50
4 14 2 45
5 13 3 20
6 14 3 46

For the above data set I want it to return only itemID 1.
That is the only item where question 13 is less than 50 question and 14 equals 45.

I was thinking of something along these lines

SELECT itemID FROM Answers
INNER JOIN filters ON questions.questionID = filters.questionID
AND questions.answer ##operator## filters.constant

Where ##operator## is the value from the operator column for that particular row.

I was thinking of a CASE statement, but I don’t believe the syntax will allow that.

Can anyone think of a way of obtaining the results I’m looking for without resorting to dynamic SQL.

Many thanks in advance

Odhran

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-11-16 : 17:07:11

Here's my crazy solution. It relies on you having a limited set of operators that can ideally be enforced with a constraint (=,<,<=,>,>=). If there are other operators you want to support, you could simply modify the logic, but each operator you add becomes a code change.

[CODE]
IF OBJECT_ID('tempdb.dbo.#filters') IS NOT NULL DROP TABLE #filters;
IF OBJECT_ID('tempdb.dbo.#answers') IS NOT NULL DROP TABLE #answers;

CREATE TABLE #filters
(
filterID int,
questionID int,
operator varchar,
constant float
)

CREATE TABLE #answers
(
answerID int,
questionID int,
itemID int,
answer float
)

INSERT #filters
VALUES
(1,13,'<',50),
(2,14,'=',45);

INSERT #answers
VALUES
(1, 13, 1,10),
(2, 14, 1,45),
(3, 13, 2,50),
(4, 14, 2,45),
(5, 13, 3,20),
(6, 14, 3,46)

DECLARE @min_increment float = 1E-14;
DECLARE @low_bound float = -1.79E+308;
DECLARE @high_bound float = 1.79E+308;

-- Option 1 -- VERY LIMITED EXAMPLE showing BETWEEN
-- Greatly depends on how many operators you will allow
SELECT a.itemID
FROM #filters
AS f
JOIN #answers
AS a
ON f.questionID
= a.questionID
GROUP BY a.itemID
HAVING SUM
(
CASE
WHEN a.answer
BETWEEN CASE f.operator
WHEN '<' THEN @low_bound
WHEN '<=' THEN @low_bound
WHEN '>' THEN f.constant + @min_increment
WHEN '>=' THEN f.constant
ELSE f.constant
END
AND CASE f.operator
WHEN '<' THEN f.constant - @min_increment
WHEN '<=' THEN f.constant
WHEN '>' THEN @high_bound
WHEN '>=' THEN @high_bound
ELSE f.constant
END
THEN 0
ELSE 1
END
) = 0

IF OBJECT_ID('tempdb.dbo.#filters') IS NOT NULL DROP TABLE #filters;
IF OBJECT_ID('tempdb.dbo.#answers') IS NOT NULL DROP TABLE #answers;
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources