How to extract a values from the table

Question

How to extract a values from the table

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
July 28, 2010 at 11:52 am

#221817

Hi,
I am having the table name as 'routetable' with the following value.
Ex.
Route Places
12G P1,P2,P3,P4,P5,P6,P6,P7,P8,P9,P10
12B P11,P12,P13,P14,P15,P16,P17
If customer chooses any two values it should bring the according Route.
I dont know how to do this? Can you please help on this.
I am trying this:
Select Route from routetable where places like '%p1' and '%p2'
It is notworking,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

Can you post DDL?

I THINK your routeplaces table has a row for each place, but the way you posted the sample data makes it look like there is one row per route with multiple places, which would be very bad design.

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 2

Yes. I agree.

Table Name: busroute

It has two columns: busno and places.

Busno Places

12G Kalaignar Nagar, Nesapaakam, Ashok Pillar,West Mambalam, T.Nagar, Anna Square

37D Kalaignar Nagar, Nesapakkam, Ashok Pillar, Kodambakkam, Valluvar Kottam, Vallalar Nagar.

Select busno from busroute where

places=(Select places from busroute where places like'%Vallalar Nagar%')

and

places=(Select places from busroute where places like'%Kodambakkam%')

This is working.

Suppose if the subquery returns more than one value, How to handle this? Can any one Help on this......

For eg.,

Select busno from busroute where

places=(Select places from busroute where places like'%Kalaignar Nagar%')

and

places=(Select places from busroute where places like'%Ashok Pillar%')

It is not working since sub query return more than one value. Actually it should return.

12G and 37D both..

I am meeting the deadline. Can any one help on this.

Jing. SSC Eights! Points: 956 More actions · Answer 3

You need to change: 'where places =' to 'where places in '

See example code below

--create table

create table busroute

(busno varchar(10),

places varchar(500))

--insert value

insert into busroute

select '12G', 'Kalaignar Nagar, Nesapaakam, Ashok Pillar,West Mambalam, T.Nagar, Anna Square'

union

select '37D', 'Kalaignar Nagar, Nesapakkam, Ashok Pillar, Kodambakkam, Valluvar Kottam, Vallalar Nagar'

--select

Select busno from busroute where

places in (Select places from busroute where places like'%Kalaignar Nagar%')

and

places in(Select places from busroute where places like'%Ashok Pillar%')

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Having a table with a column of comma delimited values is very poor design. It really goes against the whole purpose of using a relational database.

The table should be designed so that each record is a combination of the bus and one particular stop with any additional information specifically relating to that stop. From that you would be able to put together a list of your stops for the associated bus(es).

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 5

That will take much work. right? If you have any good idea can you share me how to design the table in easy way...

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 6

this will work

Select Route from routetable where places like'%Kalaignar Nagar%' AND places like'%Kodambakkam%'

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 7

Wouldn't it be easier to split the string and do something like

;WITH cteTally (N) AS

(SELECT Number FROM master..spt_values WHERE Type = 'P')

SELECT DISTINCT BusNo FROM busroute

CROSS APPLY

(

SELECT LTRIM(SUBSTRING(Places + ',', N, CHARINDEX(',', Places + ',', N) - N))

FROM cteTally

WHERE N < LEN(Places) + 2 AND SUBSTRING(',' + Places + ',', N, 1) = ','

) AS X (Place)

WHERE Place IN ('Kalaignar Nagar', 'Ashok Pillar')

Ben Teraberry SSChampion Points: 11845 More actions · Answer 8

It's really surprising to me that so many people are suggesting "solutions" based on such an incredibly flawed design. I understand that sometimes you just need to get things done, but this project is obviously early in the design phase and this first step is horribly wrong.

Also, there is a huge issue in that none of the proposals account for 'from' and 'to'. That is, if you're looking for a route and you want to go from city A to B, the order of those cities on the route is very important. To come up with a route that includes the cities but in the wrong order would not help.

To the OP ... you really need to do some reading before you start designing. Even starting with wiki articles on relational databases and normalization will help you to understand these concepts much better.

I'm not going to completely flesh this out, but here would be an example ...

a table to store information about the buses ...

create table #bus

(busIDINT IDENTITY PRIMARY KEY

,busNameVARCHAR(20)

,busModel VARCHAR(50))

insert into #bus (busName, busModel)

select 'RCC50', 'Super Duper 42' union all

select 'MPX15', 'Super Duper 42'

a table to store the information about the stop locations ...

create table #routeStop

(stopIDINT IDENTITY PRIMARY KEY

,stopNameVARCHAR(50))

insert into #routeStop (stopName)

select 'Atlanta' union all

select 'Chicago' union all

select 'Cleveland' union all

select 'Denver' union all

select 'Detroit' union all

select 'Kansas City' union all

select 'Miami' union all

select 'Memphis' union all

select 'New York' union all

select 'Toronto' union all

select 'Trenton'

a table that relates the buses to the stop locations including the stop order ...

create table #stopSequence

(sequenceIDINT IDENTITY PRIMARY KEY

,busIDINT

,stopIDINT

,stopOrderINT)

insert into #stopSequence (busID, stopID, stopOrder)

select 1, 7, 1 union all

select 1, 1, 2 union all

select 1, 8, 3 union all

select 1, 6, 4 union all

select 1, 2, 5 union all

select 1, 5, 6 union all

select 1, 10, 7 union all

select 1, 9, 8 union all

select 1, 11, 9 union all

select 2, 4, 1 union all

select 2, 2, 2 union all

select 2, 6, 3 union all

select 2, 8, 4 union all

select 2, 1, 5 union all

select 2, 7, 6

Now we can get the data we want. Not only do we get to avoid "like '%location%'" and other horrible string functions, but we can verify that the stops are in the correct order of where we want to go ...

declare @start varchar(50) = 'Chicago'

declare @end varchar(50) = 'Atlanta'

select startLoc.busID

from

(select busID,

stopOrder

from #routeStop rs

join #stopSequence ss

on ss.stopID = rs.stopID

where rs.stopName = @start) startLoc

join

(select busID,

stopOrder

from #routeStop rs

join #stopSequence ss

on ss.stopID = rs.stopID

where rs.stopName = @end) endLoc

on endLoc.busID = startLoc.busID

and endLoc.stopOrder > startLoc.stopOrder

*edit: typo

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]