"and" operation on same column

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

"and" operation on same column

Author

Topic

mysticalsun2000
Starting Member

5 Posts

Posted - 2006-09-10 : 12:53:16

Hi there,

I am trying to check weather two strings exist in a column. Like I have a table where I have four columns Train Number,Stations, Time train arrives at station and time when it departs from station. Like
train number 1 goes from bombay to x reaches bombay at 10.20 and departs at 10.22. Now i want to find out which trains travel from Calcutta to X

Train Number Station Arrival Departure
1 Bombay 10.20 10.22
1 Calcutta 11.43 11.47
1 Howrah 11.57 11.58
1 x 12.20 12.25

2 Calcutta 06.48 06.51
2 y 07.49 07.52
2 z 08.20 08.22
2 a 09.20 09.23
2 x 10.12 10.25

3 x 14.42 14.44
3 u 15.53 15.57
3 l 16.57 17.00
3 Calcutta 18.08 18.09

In this way i have a huge database, I want a query which should search for trains between Calcutta and X (it should return Train Number) but it should not return trains running between X and calcutta like in the above eg it should return 1 and 2 only but not 3. Can anyone please help.

Thanks in advance.

Amit

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-10 : 21:30:23

[code]
declare @table table
(
Train_Number int,
Station varchar(20),
Arrival varchar(5),
Departure varchar(5)
)

insert into @table
select 1, 'Bombay', '10.20', '10.22' union all
select 1, 'Calcutta', '11.43', '11.47' union all
select 1, 'Howrah', '11.57', '11.58' union all
select 1, 'x', '12.20', '12.25' union all

select 2, 'Calcutta', '06.48', '06.51' union all
select 2, 'y', '07.49', '07.52' union all
select 2, 'z', '08.20', '08.22' union all
select 2, 'a', '09.20', '09.23' union all
select 2, 'x', '10.12', '10.25' union all

select 3, 'x', '14.42', '14.44' union all
select 3, 'u', '15.53', '15.57' union all
select 3, 'l', '16.57', '17.00' union all
select 3, 'Calcutta', '18.08', '18.09'

declare @from varchar(10),
@to varchar(10)

select @from = 'Calcutta',
@to = 'x'

select *
from @table f inner join @table t
on f.Train_Number = t.Train_Number
and f.Arrival < t.Arrival
where f.Station = @from
and t.Station = @to

/* Result
Train_Number Station Arrival Departure Train_Number Station Arrival Departure
------------ -------------------- ------- --------- ------------ -------------------- ------- ---------
1 Calcutta 11.43 11.47 1 x 12.20 12.25
2 Calcutta 06.48 06.51 2 x 10.12 10.25

(2 row(s) affected)
*/[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources