Select statement which joins to a another table with where definitions inside

Question

I have a table which contains the where selection. For example ExpressionTable:

ID      WhereCase
------------------
1       = 4 
2       in(2,3)
3       = 3
4       in(4,5,6)

Now I need to select from another table with this WhereCase.

Select * from tablexy join ExpressionTable as et on tablexy.ID = et.ID
Where Country (this (WhereCase) from the ExpressionTable)

When I write where Country = et.WhereCase is not working...

What is the best way for this?

What is not working? Does it throw an error or the query doesn't return anything? — Radu Gheorghiu, Jun 18 '13 at 10:00

score 1 · Answer 1 · answered Jun 18 '13 at 11:25

1

What is the best way for this...

Don't do it.

Based on the example expressions you have provided you can easily store this information in a relational format that can then be joined onto (or appended onto the query with an EXISTS clause to keep the same semantics with respect to Duplicates).

CREATE TABLE XyCountries
  (
     XyID    INT,
     Country INT
  );

INSERT INTO XyCountries
VALUES      (1,4),
            (2,2),
            (2,3),
            (3,3),
            (4,4),
            (4,5),
            (4,6);

answered Jun 18 '13 at 11:25

Martin Smith

438,706
87
741
845

This is the best answer. Don't know why someone downvoted it. – Tobsey Jun 18 '13 at 11:29
@Tobsey - Fairly sure it was an unrelated revenge downvote for a comment I left on a nonsensical answer [to this question](http://stackoverflow.com/q/17165891/73226) as another old question of mine was downvoted at the same time and the answer was also deleted at that time. – Martin Smith Jun 18 '13 at 11:32

score 0 · Answer 2 · answered Jun 18 '13 at 10:04

0

you cant have dynamic query conditions. The only way to achieve what you want is to use dynamic SQL where you build your query in a string and them execute it like EXEC ('select 1')

answered Jun 18 '13 at 10:04

Diego

34,802
21
91
134

score 0 · Answer 3 · answered Jun 18 '13 at 10:17

0

You will need to build a SQL statement using Dynamic SQL and then execute it, eg

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = 'Select * from tablexy Where Country ' + et.WhereCase FROM ExpressionTable WHERE ID = ?

Then execute:

EXEC(@SQL)

answered Jun 18 '13 at 10:17

Julian Joseph

174
6

Select statement which joins to a another table with where definitions inside

3 Answers3