WHERE Clause

Question

WHERE Clause

chris_taylor5000

Grasshopper

Points: 20
More actions
May 26, 2010 at 4:26 am

#225068

Dear Group,
I have a SQL syntax question please that relates to the WHERE clause.
I have a table as follows:
TABLE1
col1,col1, code
ab11
cd22
ef33
gh44
ij55
I can perform a simple query like this:
SELECT col1, col2
FROM TABLE
WHERE code = '11'
However, because of issues with the application the code in the WHERE clause could end up being 66 or 77 (not a value in the table). If it's 66 I need to return rows with code 11 and 22 and if it's 77 I need to return rows with code 33, 44 and 55
How can I do this? I have tried putting CASE and IF ELSE in the WHERE clause but nothing working yet.
Many thanks,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please fully identify the entity - which you want to use in your WHERE clause - which can have values of 66 or 77. You've referred to it loosely as 'code' in your text but I think you're possibly confusing a variable with a column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Not sure exactly what you want here, but I think this'll get you started.

--Create Dummy data

--Really, you should've supplied something like this in

--your question.

DECLARE @code INT

DECLARE @Table1 TABLE(

col1 CHAR(1),

col2 CHAR(1),

code INT)

INSERT INTO @Table1

VALUES ('a',

'b',

11)

INSERT INTO @Table1

VALUES ('c',

'd',

22)

INSERT INTO @Table1

VALUES ('e',

'f',

33)

INSERT INTO @Table1

VALUES ('g',

'h',

44)

INSERT INTO @Table1

VALUES ('i',

'j',

55)

--Query

SELECT col1,

col2

FROM @Table1

WHERE code = CASE @code

WHEN 66 THEN 11

WHEN 77 THEN 22

ELSE 11

END

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

chris_taylor5000 Grasshopper Points: 20 More actions · Answer 3

Ok, thank you for the guidance on posting questions, I see your point.

From my original post:

".......If it's 66 I need to return rows with code 11 and 22 and if it's 77 I need to return rows with code 33, 44 and 55....."

So I am trying to do something like this:

--Query

SELECT col1,

col2

FROM @Table1

WHERE code = CASE @code

WHEN 66 THEN 11 AND 22

WHEN 77 THEN 33 AND 44 AND 55

ELSE 11

END

but as you would expect, the AND in the CASE statement isn't working

Many thanks

Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This works, but it looks horrible:

DROP TABLE #TABLE1

CREATE TABLE #TABLE1 (col1 CHAR(1), col2 CHAR(2), code INT)

INSERT INTO #TABLE1 (col1, col2, code)

SELECT 'a', 'b', 11 UNION ALL

SELECT 'c', 'd', 22 UNION ALL

SELECT 'e', 'f', 33 UNION ALL

SELECT 'g', 'h', 44 UNION ALL

SELECT 'i', 'j', 55

DECLARE @Code INT

SET @Code = 66

SELECT t.col1, t.col2, t.code

FROM #TABLE1 t

WHERE (@Code = 66 AND t.code IN (11,22))

OR (@Code = 77 AND t.code IN (33,44,55))

OR t.Code = @Code

If I were you, I'd use the hard-coded values to build a temporary lookup table and INNER JOIN to it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden