SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how design query


how design query

Author
Message
buntymanoj
buntymanoj
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34927 Visits: 7660
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
Henrico Bekker
Henrico Bekker
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11077 Visits: 5129
post your query?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
buntymanoj
buntymanoj
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
buntymanoj
buntymanoj
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 12
Got it Thanks every Cool Hehe ;-) :-P :-D :-)

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search