Re: [sqlite] Edinburgh Buses - SOLVED!!!
Thanks Petite for reminding me of the appropriate etiquette for using the service and thank you also for providing bonus reading materials for my perusal. Both thoughts are highly appreciated. Be well. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Edinburgh Buses - SOLVED!!!
Igor, I can't thank you enough for getting my head 'out of the box'. Your insightful remarks and assistance are highly appreciated. I made the following (underlined) changes to your script to include the transfer stops (Xfer.name): SELECT distinct StartOfR1.num, StartOfR1.company, Xfer.name xfer_name, EndOfR2.num, EndOfR2.company FROM stops Start, stops Xfer, stops Finish, route StartOfR1, route EndOfR1, route StartOfR2, route EndOfR2 WHERE Start.name='Craiglockhart' AND Finish.name='Sighthill' AND StartOfR1.stop= Start.id -- R1 actually visits Start AND EndOfR1.num = StartOfR1.num -- no transfer on the same route AND EndOfR1.stop= StartOfR2.stop -- R2 starts where R1 ends AND EndOfR1.num != StartOfR2.num -- R1 and R2 are not the same route AND EndOfR1.stop = Xfer.id-- R1 changes to R2 AND EndOfR2.company = StartOfR2.company -- R1 changes bus to R2 AND EndOfR2.num = StartOfR2.num -- two stops on the same route AND EndOfR2.stop = Finish.id -- R2 actually visits Finish; Igor, because of your help, I was able to follow your succinct logic and complete this very difficult and complicated exercise (for me :-)). Thank you for your mentorship. You have been added to the other 2 stars on this site. Be well. Errol Emden ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Edinburgh Buses
I have taken a different approach to the solution to this problem. Instead of paring the self-joins I have cross-joined the tables in pairs as follows: SELECT distinct R1.num, R1.company, S1.name, R2.num, R2.company FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Craiglockhart' AND S2.name='Sighthill' AND R1.stop=S1.id AND R2.stop=S2.id; However, I can't find a way to identify the stops (S3.name) where transfers. occur. Also, Rw.num and R2.company remain static. Any idea what I am doing wrong here? From: eem...@hotmail.com To: sqlite-users@sqlite.org Subject: RE: [sqlite] Edinburgh Buses Date: Tue, 26 Aug 2014 11:03:07 -0400 Igor, thanks for a meaningful response. I am a neophyte at SQL, and learning it on my own. The graphical approach to a solution is another methodology I will be adding to my arsenal. The reason why I was checking both names against stop B is that my intent was to find the other route stops that connected to these stops. I am now aware that these connections are not possible. Errol Emden > To: sqlite-users@sqlite.org > From: i...@tandetnik.org > Date: Mon, 25 Aug 2014 20:05:40 -0400 > Subject: Re: [sqlite] Edinburgh Buses > > On 8/25/2014 7:52 PM, Errol Emden wrote: > > FROM stops astop > > JOIN stops bstop ON bstop.name='Craiglockhart' OR bstop.name='Sighthill' > > Why are you checking two names against stop B, and none against stop A? > > You also need to check that route A and route B actually connect - that > the end stop of one is the start stop of the other. > > I suggest you draw a picture, then write down all the conditions that > need to be true to ensure that all the dots and lines connect to each > other the right way. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Edinburgh Buses
Igor, thanks for a meaningful response. I am a neophyte at SQL, and learning it on my own. The graphical approach to a solution is another methodology I will be adding to my arsenal. The reason why I was checking both names against stop B is that my intent was to find the other route stops that connected to these stops. I am now aware that these connections are not possible. Errol Emden > To: sqlite-users@sqlite.org > From: i...@tandetnik.org > Date: Mon, 25 Aug 2014 20:05:40 -0400 > Subject: Re: [sqlite] Edinburgh Buses > > On 8/25/2014 7:52 PM, Errol Emden wrote: > > FROM stops astop > > JOIN stops bstop ON bstop.name='Craiglockhart' OR bstop.name='Sighthill' > > Why are you checking two names against stop B, and none against stop A? > > You also need to check that route A and route B actually connect - that > the end stop of one is the start stop of the other. > > I suggest you draw a picture, then write down all the conditions that > need to be true to ensure that all the dots and lines connect to each > other the right way. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Edinburgh Buses
Goodly Folks, I am confounded by the required solution to the following problem: Find the routes involving two buses that can go from Craiglockhart to Sighthill. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus. The database schema is as follows: stops(id, name); route(num,company,pos, stop) route.stop is the foreign key for stops.id and is numeric. num is the route number, an alphanumeric string. name is the name of stops.id, e.g. id 149 is 'London Road'. My attempt does not provide the answer: SELECT a.num, a.company, astop.name, b.num, b.company FROM stops astop JOIN stops bstop ON bstop.name='Craiglockhart' OR bstop.name='Sighthill' JOIN route a ON a.stop=bstop.id JOIN route b ON b.stop=astop.id; The output should look like the following: numcompanynamenumcompany4LRTLondon Road34LRT4LRTLondon Road35LRT4LRTLondon Road65LRT4LRTLondon RoadC5SMT4LRTPrinces Street3LRT4LRTPrinces Street3ALRJThe approach I am told is to self-join twice to find buses that visit Craiglockhart and Sighthill, then join those on matching stops. I have no idea how to do this and would like to know how to do this as well as to find an even easier way to solve this problem. Your assistance will be highly appreciated. Be well. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UEFA EURO 2012 Football Championship problem - SOLVED!!!
Thanks Petite...You, like Keith Medcalf, are a great mentor, helping me to develop my burgeoning skills in SQL. I appreciate the reason for the cause of each of the problems I encountered and how to correct them. 'Granularity' is a new term for me and I now know why multiple entries occurred. Keith earlier introduced single 'scalar' result of correlated sub-queries. Also, I am now more aware of the type of join that should be used to include desired rows. Be well. > From: petite.abei...@gmail.com > Date: Mon, 11 Aug 2014 21:39:29 +0200 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] UEFA EURO 2012 Football Championship problem > > > On Aug 11, 2014, at 8:39 PM, Errol Emden <eem...@hotmail.com> wrote: > > > 1. Matches in which neither team scored is not being displayed. > > Because you have an inner join to goal. If there no goal, then no entry will > match. > > > 2. Scores for the same matchid where both teams scored are appearing on > > separate lines instead of in a single line. > > Because you have a join to goal, which has a granularity of one entry per > goal, per match. So, if multiple goal, multiple entries. You try to > compensate by grouping per match and team, so you end up with two entries if > both team have scored. > > > What do I need to do to correct these issues? > > Get you granularity in order. > _ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UEFA EURO 2012 Football Championship problem - SOLVED!!
Keith...you are the man!!! Thanks for showing me a better way to solve my problem. I honestly did not think of using correlated subqueries, mainly because I was unsure of the underlying operations when they are used. But your mentoring explanation has made it quite clear now. I hope to be as good as you are...one day :-). Be well. > Subject: RE: UEFA EURO 2012 Football Championship problem > Date: Mon, 11 Aug 2014 17:47:30 -0600 > From: kmedc...@dessus.com > To: eem...@hotmail.com > CC: sqlite-users@sqlite.org > > How about > > select mdate, > team1, > (select count(*) > from goal >where matchid = game.id > and teamid = game.team1) as score1, > team2, > (select count(*) > from goal >where matchid = game.id > and teamid = game.team2) as score2 > from game > order by mdate, id, team1, team2; > > the parts "(select ...) as scoreX" are called Correlated Subqueries. > Basically, you are retrieving your game table and columns that you want from > it. For each returned row each Subquery is executed to return the result > computed for the value(s) contained in that row. A Correlated Subquery may > only return a single scalar result (ie, only one row containing one value). > If a subquery returns multiple rows, only the first value is used (that is, > it is as if the Correlated Subqueries had "limit 1" appended to them). > Selecting multiple values in a correlated subquery is a syntax error. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UEFA EURO 2012 Football Championship problem
Hi Keith: I am trying to list every match with the goals scored by each team as shown. mdateteam1score1team2score2 1 July 2012ESP4ITA 0 10 June 2012ESP1ITA1 10 June 2012IRL1CRO3 ... The database schema is as follows: game(id,mdate,stadium,team1,team2); goal(matchid,teamid,player,gtime); eteam(id,teamname,coach). matchid=game.id; teamid=eteam.id; team1 and team2 are teamids'; gtime is time each goal is scored in a match. My problems in the SQL code below are: 1. Matches in which neither team scored is not being displayed. 2. Scores for the same matchid where both teams scored are appearing on separate lines instead of in a single line. What do I need to do to correct these issues? SELECT mdate, team1, CASE WHEN teamid=team1 THEN count(gtime) ELSE 0 END score1, team2, CASE WHEN teamid=team2 THEN count(gtime) ELSE 0 END score2 FROM game JOIN goal ON matchid = id GROUP BY matchid, teamid ORDER BY mdate,matchid,team1,team2 Thanks and be well. Errol ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
Keith...as a neophyte, I am totally blown away by your skill level in SQL and the ease with which you explain its underlying operations. I am extremly impressed by the 'flattening of the query' and its operation...it worked perfectly!!. I am so happy that this forum has persons of your caliber who totally understands SQL and can provide efficient solutions to problems forum members, like myself, may have. Again, thanks and be well. > Subject: RE: [sqlite] What am I doing wrong? > Date: Fri, 8 Aug 2014 17:40:28 -0600 > From: kmedc...@dessus.com > To: eem...@hotmail.com > CC: sqlite-users@sqlite.org > > > Leaving out the extra fields the movie table, enforcing referential > integrity, and using "same column names" to hold the same data, and declaring > the right indexes, you can get a much more efficient solution, both in the > expression and the execution: > > These optimizations are only necessary *after* you have solved the problem, > and then really only if performance is inadequate. Enforcement of > referential integrity and ensuring good indexes should always be done, of > course. _ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
Keith...thanks vmuch for your insightful and enlightening approach of translating 'in parts'. I am truly indebted to you for your mentoring approach...be well. > Date: Fri, 8 Aug 2014 15:03:13 -0600 > From: kmedc...@dessus.com > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] What am I doing wrong? > > >I am to list the film title and the leading actor for all of the films > >'Julie Andrews' played in. > > >The following is structure of the tables used: > > >movie(id, title, yr, director, budget, gross); > >actor(id, name); > >casting(movieid, actorid, ord). > >The column name ord has a value of 1 if the actor is in starring role. > > Translate the English in parts: > > "all the films "Julie Andrews" olayed in > -> > select movie.id > from movie, casting, actor > where movie.id = casting.movieid >and casting.actorid = actor.id >and actor.name = 'Julie Andrews' > > then wrap the part "list the film title and the leading actor" around that > list of films: > > select movie.name, actor.name > from movie, casting, actor > where movie.id = casting.movieid >and casting.actorid = actor.id >and casting.org = 1 >and movie.id in (select movie.id > from movie, casting, actor > where movie.id = casting.movieid >and casting.actorid = actor.id >and actor.name = 'Julie Andrews'); > > Being able to translate problem statements from well formed human language > into well formed computer language statement(s) "The Solution" is what > separates "programmers" from "coders". The problem statement does not > require any ordering of the results, so they are in visitation order (which > is not random, but may appear to be random to "coders"). > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What am I doing wrong?
The SQL script you wrote actually provides the same information as mine - it
lists all movies that Julie Andrews is in but it does NOT provide who is the
leading actor in each movie, as all names selected is that of Julie Andrews.
> Date: Fri, 8 Aug 2014 20:55:55 +0200
> From: luu...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What am I doing wrong?
>
> On 8-8-2014 20:35, Errol Emden wrote:
> >
> >
> >
> > I am to list the film title and the leading actor for all of the films
> > 'Julie Andrews' played in. The following is structure of the tables used:
> > movie(id, title, yr, director, budget, gross);
> > actor(id, name);
> > casting(movieid, actorid, ord). The column name ord has a value of 1 if the
> > actor is in starring role.
> >
> > My attempt at a solution, produces all the movie titles but the actors'
> > names are all for Julie Andrews:
> >
> > titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie
> > AndrewsDuet for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie
> > AndrewsMary PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie
> > AndrewsShrek the ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of
> > EmilyJulie AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess
> > DiariesJulie AndrewsThe Princess Diaries 2: Royal EngagementJulie
> > AndrewsThe Sound of MusicJulie AndrewsThe Tamarind SeedJulie
> > AndrewsThoroughly Modern MillieJulie AndrewsTooth FairyJulie AndrewsTorn
> > CurtainJulie AndrewsVictor VictoriaJulie Andrews
> > The SQL code for the above is given below:
> > SELECT distinct m1.title, a1.name FROM casting c1
> > JOIN movie m1 on m1.id=c1.movieid
> > JOIN actor a1 on a1.id=c1.actorid
> > WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
> > SELECT a2.name FROM actor a2
> > WHERE c1.ord<>1 and a2.id=a1.id));
> >
> > Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
> > Despicable Me, etc.? I should appreciate your help in finding the
> > appropriate solution. Thanks and be well.
> >Errol Emden
> >
>
> This should list all movies with 'Julie Andrews' in a role
> (starring, or not):
>
> SELECT distinct m1.title, a1.name FROM casting c1
> JOIN movie m1 on m1.id=c1.movieid
> JOIN actor a1 on a1.id=c1.actorid
> WHERE a1.name='Julie Andrews'
>
> Can you explain why you added the part that i erased from the query?
>
> Because, basically adding
> 'she should be starring' (ord=1)
> or
> 'she should have a role not starring'
> (c1.ord<>1 and a2.id=a1.id)
>
> will make things unnecessarily complicated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What am I doing wrong?
I am to list the film title and the leading actor for all of the films 'Julie
Andrews' played in. The following is structure of the tables used:
movie(id, title, yr, director, budget, gross);
actor(id, name);
casting(movieid, actorid, ord). The column name ord has a value of 1 if the
actor is in starring role.
My attempt at a solution, produces all the movie titles but the actors' names
are all for Julie Andrews:
titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie AndrewsDuet
for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie AndrewsMary
PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie AndrewsShrek the
ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of EmilyJulie
AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess DiariesJulie
AndrewsThe Princess Diaries 2: Royal EngagementJulie AndrewsThe Sound of
MusicJulie AndrewsThe Tamarind SeedJulie AndrewsThoroughly Modern MillieJulie
AndrewsTooth FairyJulie AndrewsTorn CurtainJulie AndrewsVictor VictoriaJulie
Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
SELECT a2.name FROM actor a2
WHERE c1.ord<>1 and a2.id=a1.id));
Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
Despicable Me, etc.? I should appreciate your help in finding the appropriate
solution. Thanks and be well.
Errol Emden
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
