Problem with the "IN" clause - 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

Transact-SQL (2000)

Problem with the "IN" clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-27 : 12:34:04

Sandeep writes "hi,
i have a query like
"select * from abc where a in('a','b','c')"

and in my table abc i only have records corresponds to a and c

so the reslut will be only 2 rows as against 3 parameter that i passed

can I get a query that will return the 3 rows and if the query did n't find any record then it return a blank row so that my result count is 3.

so the result that i want is:
a
<blank>
c

How can i acheive this type of result?

Thanking you in advance
sandeep"

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 12:50:11

Not sure of the pratical application for this, but....

Your values a,b,c I would consider a type of validation rule. These rules could be stored in a table such that:

CREATE TABLE TABLE1(Col1 char(1))
GO
INSERT INTO TABLE1(Col1) VALUES('a')
INSERT INTO TABLE1(Col1) VALUES('b')
INSERT INTO TABLE1(Col1) VALUES('c')
GO

Then you could use simple SQL to do what you're asking (what are you asking?):

And the statement would look like:

SELECT r.* FROM TABLE1 l LEFT JOIN abc r ON l.COL1 = r.a

Hope this helps

Brett

8-)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-02-27 : 12:54:55

The following is a fudge but will get the you the answer given the situation you've described. however it may not easily be expandable.

select * from tablename a
right outer join
(select s.name1 from
(select 'a' as name1
union
select 'b' as name1
union
select 'c' as name1) s) t
on t.name1 = a.name

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-27 : 13:38:29

I am not sure exactly what you are trying to get at either, but a lot of times people apply filters to their data and then don't understand why records are dropped. They still want to SEE all the records, but they also want their criteria applied.

Consider this:

SELECT ID, Value
FROM Table
WHERE Value > 40

versus this:

SELECT ID, CASE WHEN Value > 40 THEN Value ELSE Null END as Value
FROM Table

The idea is to not filter the records so you get a consistent row count, but rather to filter certain FIELDS as you need, so that all ID's are always returned.

Again, I have no idea if this is what you are looking for, but maybe it will help. if it doesn't, definitely give us some more information.

Edited by - jsmith8858 on 02/27/2003 13:39:19

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-27 : 13:40:59

Jeff, unless I miss my guess, you posted an answer from a different post here?

Or am I totally lost

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources