SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Please help me		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WAK
Starting Member

2 Posts
Posted - 06/13/2005 :  22:50:40  Show Profile  Reply with Quote
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)

USA
6997 Posts
Posted - 06/13/2005 :  23:11:52  Show Profile  Reply with Quote
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
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts
Posted - 06/13/2005 :  23:14:25  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000