Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how design query Expand / Collapse
Author
Message
Posted Monday, November 08, 2010 12:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:23 PM
Points: 3, Visits: 12
hi all
below i am showing my bus route table now how i can find out which route will come if i user want to go B to D stop (If user choose B to D stop then route no 1 will come)

Id Route Stops Stopno
1 1 A 1
2 1 B 2
3 1 C 3
4 1 D 4
5 1 E 5
6 2 A 5
7 2 B 4
8 2 C 3
9 2 D 2
10 2 E 1
Post #1017014
Posted Monday, November 08, 2010 12:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
Bunty, you left out too much of the question for us to even point you in the right direction, because if I read this right, you CAN go from B to D. If you're going to ask homework questions, at least do it properly, see the first link in my signature.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1017015
Posted Monday, November 08, 2010 12:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
post your query?

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1017016
Posted Monday, November 08, 2010 12:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:23 PM
Points: 3, Visits: 12
select distinct t1.routeno from Table_1 t1 inner
join Table_1 t2 on t2.stop=t1.stop
where
(t1.stop='D' and t1.stopno<t2.stopno)
or(t1.stop='E' and t1.stopno>t2.stopno )

problem in this query is its showing me route no 2 also

select distinct t1.routeno from Table_1 t1 inner
join Table_1 t2 on t2.stop=t1.stop
where
(t1.stop='A' and t1.stopno<t2.stopno)
or(t1.stop='E' and t1.stopno>t2.stopno )

This Query running fine

there is to route (route no 1 and route no 2 )
Route no 1 Bus going (A stop to E Stop) and
Route no 2 Bus going (E stop to A Stop)

If user want to go stop A to E or D to E then route no 1 will come. but i am not avail to find out how i design my query.
Post #1017019
Posted Monday, November 08, 2010 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:23 PM
Points: 3, Visits: 12
Got it Thanks every

select distinct t1.*,T2.* from Table_1 t1 inner
join Table_1 t2 on t1.routeno=t2.routeno
where (t1.stop='A' ) and (t2.stop='E')
and t2.stopno>t1.stopno
order by t1.routeno


select distinct t1.*,T2.* from Table_1 t1 inner
join Table_1 t2 on t1.routeno=t2.routeno
where (t1.stop='D' ) and (t2.stop='E')
and t2.stopno>t1.stopno
order by t1.routeno
Post #1017584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse