Please help me - 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 2000 Forums

SQL Server Development (2000)

Please help me

Author

Topic

WAK
Starting Member

2 Posts

Posted - 2005-06-13 : 22:50:40

I have a table(Test) with four columns(ID, OP1, OP2, OP3) which contains options structure is like this.

ID OP1 OP2 OP3
--- ---- ---- ----
1 NULL NULL NULL
2 ABC NULL NULL
3 DEF DEF NULL
4 GHI GHI GHI

I have to retrieve ID from test table where a string of three characters is in OP1 and OP2 and OP3 column but if value is null in the next column no need to check further.

Example: I want to pick ID for string DEF. So my condition in query should become.
select ID from test where OP1 = 'DEF' and OP2 = 'DEF';

And if I am looking for GHI so my query should be this
select ID from test where OP1 = 'GHI' and OP2 = 'GHI' and OP3 = 'GHI';

Please help me and propose optimum query or way to achieve this result.

Thanks in advance

WAK

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-13 : 23:11:52

This should do it:


declare @search_value varchar(3)
select	@search_value = 'GHI'

Select
	ID
from
	Test
where
	OP1 = @search_value and
	(
	(OP2 is null ) or
	(OP2 = @search_value and OP3 is null ) or
	(OP2 = @search_value and OP3 = @search_value )
	)

CODO ERGO SUM

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-13 : 23:14:25

There's no way to optimally achieve this. It's a stupid design.

SELECT ID, OP1, OP2, OP3
FROM StupidTable
WHERE OP1 + OP2 + OP3 IS NOT NULL --Assumes IF you have values in all three columns, they are the same.

This is a stupid design and you should seriously reconsider using it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources