| Author |
Topic |
|
princess
Starting Member
6 Posts |
 Posted - 2006-06-18 : 22:13:18
|
| I've got three tables > school, schoolname, schoolroutes with data as follows: (number) (text)school--> id | description 38 service 38 38 newyork, sydney, paris 38 9.10 am arrive at kings avenue 38 turn left at sydney avenue 40 service 40 40 london,sydney,chicago 40 arrive at abc circuit 40 turn right at roundabout (auto no) (text)schoolname---> schoolid | name 1 kids school 2 kids primary 3 unique school (number) (number)schoolroutes----> schoolid | serviceID 51 401 53 401 11 402 72 402The query used to extract data is as follows>SELECT serviceID FROM schoolroutes WHERE schoolID = #schoolid# AND serviceID IN (SELECT distinct id FROM school WHERE description LIKE '% # suburb # %' )ORDER by serviceIDI'm not allowed to create a new table for suburbs or modify any of the tables...I'm only meant to change the query if possible. The above query brings up the information not only of the matching suburb but also information of the suburb having the same suburb name with street or avenue associated to it..For eg: in the above school table, if the search was made to extract the data exclusively for sydney suburb, it will bring the dataof service 38 as well as service 40 but not producing just for service 40 Is it possible to make the query simpler based on the above information? Thanks. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-18 : 22:15:32
|
explore JOINS, read more in Books Online or google  --------------------keeping it simple... |
 |
|
|
princess
Starting Member
6 Posts |
Posted - 2006-06-18 : 22:30:56
|
| Thanks for your suggestion..I was after a spoon-feeding precise answer to that..Can someone yell towards the query plz? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-18 : 23:03:05
|
princess, Here it is in silver spoon  select r.serviceIDfrom schoolroutes r inner join school son r.schoolid = s.idwhere r.schoolid = #schoolid#and s.description like '% # suburb # %'order by r.serviceID
KH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
princess
Starting Member
6 Posts |
Posted - 2006-06-19 : 02:20:00
|
| I appreciate your help. May be you guys didn’t understand to the problem. The task is to find the available routes between suburb and school. There is no additional suburb table neither the tables seem to be normalised. I’m only meant to modify the existing join statement.The information extracts the routes from the database. At the front end the user enters the name of suburb and the name of school to find available routes. The school table has got ‘description’ containing suburbs in it. The ‘school’ table comprises of timetable information for bus route numbers. I’ve used names of suburbs as major city names New York, Sydney, Paris to show how the data looks like… --(number) ---(text)school--> id | description 38 ---- service 38 38 --- newyork, sydney, paris 38 --- 9.10 am arrive at kings avenue38 --- turn left at sydney avenue38 --- turn right at holt street38 --- etc ctd… 40 --- service 4040 --- london, sydney, Chicago40 ---- arrive at northbourne circuit40 --- turn right at roundabout40 --- turn left at mc donalds40 --- etc ctd.. -------- (auto no) --- (text)schoolname---> schoolid | name 1 ---- kids school 2 ---- kids primary 3 ---- unique school 4 ---- Rosemary school 5 ---- Anthony school ------- (number) --- (number)schoolroutes----> schoolid | serviceID 5 --- 40 2 --- 40 1 --- 383 --- 38In ‘schoolroutes’ table, the serviceID seems to be the bus route number. The existing query > SELECT serviceID FROM schoolroutes WHERE schoolID = #schoolid# AND serviceID IN (SELECT distinct id FROM school WHERE description LIKE '% # suburb # %' )ORDER by serviceID works fine…but generates the data sometimes not corresponding to the suburb names. Reason? The description column of the ‘school’ table has got the same suburb name Sydney avenue as that of suburb sydney. From the front end among the list of suburbs ( Sydney, London, new york, paris , Chicago ) if Sydney was selected as the suburb and from the schools list if Anthony school was selected as the school name. Then the output should only generate the route information of 40 but not 38. At the moment, the output search brings results of 40 and 38.The data is huge large having same name as of suburbs carrying avenue, circuit, street, (London circuit, Sydney avenue, Sydney circuit etc) in the description column of school table….I’m not allowed to modify or create any new table…How can I solve this problem? Seems that I can only use join statement using not like in avenue, circuit etc…can u help me to write the query plz? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-19 : 02:51:34
|
| declare @description varchar(100), @schoolname varchar(100)select @description='suburb',@schoolname='Anthony School'select s.id from school s join schoolroutes sron s.id=sr.serviceidjoin schoolname snon sr.schoolid=sn.schoolidwhere s.description like '%' + @description + '%'and sn.[name]=@schoolname--------------------keeping it simple... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-19 : 02:56:25
|
[code]declare @school table( id int, description varchar(50))insert into @schoolselect 38 , 'service 38' union allselect 38 , 'newyork, sydney, paris' union allselect 38 , '9.10 am arrive at kings avenue' union allselect 38 , 'turn left at sydney avenue' union allselect 38 , 'turn right at holt street' union allselect 38 , 'etc ctd..' union allselect 40 , 'service 40' union allselect 40 , 'london, sydney, Chicago' union allselect 40 , 'arrive at northbourne circuit' union allselect 40 , 'turn right at roundabout' union allselect 40 , 'turn left at mc donalds' union allselect 40 , 'etc ctd..' declare @schoolname table( schoolid int, name varchar(50))insert into @schoolnameselect 1 , 'kids school' union allselect 2 , 'kids primary' union allselect 3 , 'unique school' union allselect 4 , 'Rosemary school' union allselect 5 , 'Anthony school'declare @schoolroutes table( schoolid int, serviceid int)insert into @schoolroutesselect 5 , 40 union allselect 2 , 40 union allselect 1 , 38 union allselect 3 , 38declare @schoolid int, @suburb varchar(50)select @schoolid = 5, @suburb = 'sydney'SELECT r.serviceid, s.descriptionFROM @schoolroutes r inner join @school s on r.serviceid = s.idwhere r.schoolid = @schoolidand s.description like '%' + @suburb + '%'/* RESULT :serviceid description ----------- -------------------------------------------------- 40 london, sydney, Chicago*/[/code]
KH |
|
|
|
princess
Starting Member
6 Posts |
Posted - 2006-06-19 : 03:12:44
|
| Dears...I wished i could change or modify the tables...its very poor designing...Unfortunately,i don't have the permission to touch the tables...I'm only allowed to play with the query by programming...each table has information carrying 5000 records at the least.. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-19 : 03:16:22
|
Princess, if you are referring to my post, the table variable are for testing the query. Does the query (in blue) works for you case ?
KH |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-19 : 03:17:35
|
| you're not changing the tables...if you want the exact query, provide the ddl for those 3 tables--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
princess
Starting Member
6 Posts |
Posted - 2006-06-19 : 05:03:18
|
quote:
Originally posted by madhivanan
Also read about Normalisationhttp://www.datamodel.org/NormalizationRules.htmlMadhivananFailing to plan is Planning to fail
Thanks for ur kind suggestion..Please read the forum conversation once again...I'ven't designed the tables!!! Failing to explain would obviously result in a blunt reply!!! |
|
|
|
gurusamy.senthil
Yak Posting Veteran
65 Posts |
Posted - 2006-06-19 : 06:40:45
|
| Princess, This is the forum to develop our skills and to learn from person who knows. You may strong in certain area, someone may strong in some area.Everyone wants to learn and to get clear idea from anybody who belongs to this group.Kidding maddy is not good sine. You may not clear in your explanation. may be that finds hard to explain others for your question.Explain once again and get result from seniors. |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-06-19 : 06:45:59
|
| And get some more respect for fellow members such as Madhi if i was him I would not help anyone with such a bad attitude. |
|
|
|
gurusamy.senthil
Yak Posting Veteran
65 Posts |
Posted - 2006-06-19 : 07:09:26
|
| Princess like jim said try to change your attitude. If you changed it you can reach the altitude fast.Guys cheer up, don't take is has serious, we need your cooperation and help. |
|
|
|
princess
Starting Member
6 Posts |
Posted - 2006-06-19 : 07:27:52
|
| I appreciate your advice. I’m new to this site and was after a solution..People can suggest me if anything is unclear to them…how will I come to know if no one explains or tells where I’m wrong in explaining the problem ? I haven’t visited this site blindly by not reading any books or links… People should give clear explanations rather than providing a poking attitude.Apologies to anyone…. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-06-19 : 07:43:43
|
| Did Khtan's query (in blue above) not work?-------Moo. :) |
|
|
|
|
Is it possible to make this query simpler ?