IN Statement - 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

General SQL Server Forums

New to SQL Server Programming

IN Statement

Author

Topic

Dagmd
Starting Member

14 Posts

Posted - 2007-03-08 : 05:52:30

Hello,

I am trying to use an IN statement for a set of values, however instead I need to only return a column when all values are matched. Is there a variation on IN that allows you to test for all values within your IN ' ' statement? or another way to do this besides multiple AND statements?

Thanks

Talis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:56:22

Use EXISTS keyword.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:57:24

SELECT t1.*
FROM Table1 AS t1
WHERE EXISTS (SELECT NULL FROM Table2 AS t2 WHERE t2.ColA = t1.Col1 AND t2.ColB = t1.Col2)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:58:52

Use above when comparing to other table, or use INNER JOIN.
If only comparing to a number of values, you have to use AND.

These are all guesses.
Without some proper sample data and your expected output based on the sample data, this is all we can help you with.

Peter Larsson
Helsingborg, Sweden

Dagmd
Starting Member

14 Posts

Posted - 2007-03-08 : 06:02:18

Hi Peter,

Thanks. Can you give an example how I change my statement below:-

Select * from whatever
where x=z
and
y in
('1',
'2',
'3')

into an EXISTS statement?

quote:
Originally posted by Peso

SELECT t1.*
FROM Table1 AS t1
WHERE EXISTS (SELECT NULL FROM Table2 AS t2 WHERE t2.ColA = t1.Col1 AND t2.ColB = t1.Col2)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 06:06:06

You don't have to.
It is more efficient the way you have written it.

Peter Larsson
Helsingborg, Sweden

Dagmd
Starting Member

14 Posts

Posted - 2007-03-08 : 06:38:56

Ah, but I need y (in example above) to only return if it matches all the values within the IN list.

quote:
Originally posted by Peso

You don't have to.
It is more efficient the way you have written it.

Peter Larsson
Helsingborg, Sweden

richardps
Starting Member

33 Posts

Posted - 2007-03-08 : 06:41:53

How can Y be equal to '1' and '2' and '3' all at the same time?

Can you better explain with perhaps some example values?

Thanks,
Richard.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 07:14:34

Select *, case when y in ('1','2','3') then 'Found' else 'Not found' end from whatever
where x = z

One other variant...

Please post some proper sample data and your expected output based on the sample data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 07:15:52

Or this?

select id
from (
	Select	ID from whatever where x = z and y = '1' union all
	Select	ID from whatever where x = z and y = '2' union all
	Select	ID from whatever where x = z and y = '3'
	) as x
group by id
having count(*) = 3

Peter Larsson
Helsingborg, Sweden

Dagmd
Starting Member

14 Posts

Posted - 2007-03-08 : 08:53:43

Hi again, here is a sample query that explains what I am currently doing:-

select distinct sys.name
from sys

inner join statusx on sys.resourceid = statusx.res_id
inner join wkstation-status on sys.res_id = wkstation-status.res_id

where statusx.status0 = 'true'
and statusx.id0 in

('S14',
'S015',
'S018')

quote:
Originally posted by Peso

Select *, case when y in ('1','2','3') then 'Found' else 'Not found' end from whatever
where x = z

One other variant...

Please post some proper sample data and your expected output based on the sample data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 09:12:24

What's wrong with that query?
It seems to work ok.

Peter Larsson
Helsingborg, Sweden

Dagmd
Starting Member

14 Posts

Posted - 2007-03-08 : 09:39:30

Hi yes, it does work, however - statusx.id0 must be true for each value in the IN list. At the moment - it is true if it matches any value in the list.

quote:
Originally posted by Peso

What's wrong with that query?
It seems to work ok.

Peter Larsson
Helsingborg, Sweden

richardps
Starting Member

33 Posts

Posted - 2007-03-08 : 10:59:27

Sample data required to fully understand this I think...

what is the result of select top 20 statusx.id0 - this should give a bit more of a clue.

Perhaps you are after creating a string from 'S14', 'S015', 'S016' or a like statement with %'s?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources