question

MkontwanaGcobaniEXTERNVWSAfrica-9452 avatar image
0 Votes"
MkontwanaGcobaniEXTERNVWSAfrica-9452 asked EchoLiu-msft edited

How to use where clause to get more than one value in sql server?

Hi Team

I am getting a one specific value when im using where clause. Is there a better to get other values when IN statement inside parameters is used? Please have a look at my query below

select
[Station Description]
[Transaction Code],
[DateTime Passed]
from [Tracking_Server_DB].[dbo].TS_Station as stn
inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
on stn.[Transaction Code] = mv.[Transaction Code]
where stn.[Station Description] IN ('T0011', 'M250', 'T0011')
and [DateTime Passed] = '2021-08-26 22:49:19.000'
order by [DateTime Passed] desc


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @FarhanSabir-9947

Welcome to the microsoft TSQL Q&A forum!

The WHERE clause will return all rows that meet the conditions. The parameter IN will also return all rows that meet the condition. In other words, all rows that meet the conditions behind them will be returned.

As pituach said, it may be that your JOIN statement filters out other rows. Please try the following code first to see if the JOIN sentence is incorrectly filtered:

 select
 [Station Description]
 [Transaction Code],
 [DateTime Passed]
 from [Tracking_Server_DB].[dbo].TS_Station as stn
 inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
 on stn.[Transaction Code] = mv.[Transaction Code]
 --where stn.[Station Description] IN ('T0011', 'M250', 'T0011')
 --and [DateTime Passed] = '2021-08-26 22:49:19.000'
 order by [DateTime Passed] desc

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered

Good day,

How to use where clause to get more than one value in sql server?

Simply make sure that the condition do not filter out all the rows...

The WHERE simply specifies the search condition for the rows which returned by the query. There is no limit to the number of rows which can return when you use WHERE (if there are rows that fit the condition). If you do not get all rows, then your condition in the WHERE or the condition in the INNER JOIN ON filter these rows out.

If the amount of rows in not too high then you can check manually the rows which return without the where after using the JOIN. Try to execute the same query and examine the rows. My guess is that your INNER JOIN filter some rows which you expect to get in the result, and your first thought was that filter is done by the WHERE only -I cannot know if this is the case since we don't have your table and data in roder to execute the query


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.