Oops back sooner than I thought. I would like to order the grouping of the Persons in a particular order. So the sequence of the result is based on the TeamPersonTable's order field.
TeamTable +-------------------+ | id | name | +-------------------+ | 1 | blue | | 2 | green | +-------------------| PersonTable +------------------+ | id | name | +------------------+ | 4 | john | | 5 | bill | +---------+-------+ TeamPersonTable +-----------------------------------+ | teamId | personId | order +----------------------------------+ | 1 | 4 | 1 | 1 | 5 | 0 | 2 | 4 | 0 | 2 | 5 | 1 +---------+------------+----------+ Query Input: teamId = 1 Result: personNames = "bill,john" personIds = "5,4" ------------- Query Input: teamId = 2 Result: personNames = "john,bill" personIds = "4,5" On 5/22/14, fantasia dosa <fantasia.d...@gmail.com> wrote: > RSmith, Two thumbs up for your super kind help. > > I will study the queries that you gave and try them backwards and > forwards to better understand the magic. Hope you don't mind if I ask > more newbie questions in the future. > > Cheers to the many nice people in this Great list. > > > On 5/22/14, fantasia dosa <fantasia.d...@gmail.com> wrote: >> I'm very happy to have taken the advice of the Awesome people on this >> list to add an additional table and make the data normalized. From >> today, I learned that the word Normalized -> Magic. >> >> On 5/22/14, fantasia dosa <fantasia.d...@gmail.com> wrote: >>> Ah yes, you are correct. >>> >>> It was my typo. Works perfectly. >>> >>> On 5/22/14, RSmith [via SQLite] >>> <ml-node+s1065341n75787...@n5.nabble.com> >>> wrote: >>>> >>>> >>>> I'm trying to understand the grouping magic and testing the first and >>>> last query. >>>> Only thing is for some reason, I'm not getting any result for the last >>>> query. Perhaps i'm doing something wrong. >>>> >>>> >>>> Works perfectly for me, are you sure you have it exactly so? Check the >>>> table >>>> names correspond to your tables maybe. (Though the >>>> other queries should fail too if that was a problem): >>>> Also ensure you have a Person in the PersonTable with id = 5. >>>> >>>> SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS >>>> PersonName >>>> FROM TeamPersonTable TP >>>> LEFT JOIN PersonTable P ON P.id=TP.personId >>>> LEFT JOIN TeamTable T ON T.id=TP.teamId >>>> WHERE P.id=5 >>>> >>>> >>>> >>>> On 2014/05/22 14:28, Humblebee wrote: >>>>> Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! >>>>> The first query gave the Exact answer I was looking for. It even >>>>> assembled a string. >>>> >>>> Forgot to mention: These queries and subsequent amazement is only >>>> courtesy >>>> of those tables being in 1NF format now, it is what makes >>>> the queries possible to be linked up in the way (or in any other way >>>> you >>>> might dream up) - which is why other posters tried to urge >>>> you so hard to consider changing to this. Very glad you did. >>>> >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>>> >>>> >>>> >>>> _______________________________________________ >>>> If you reply to this email, your message will be added to the >>>> discussion >>>> below: >>>> http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75787.html >>>> >>>> To unsubscribe from Simple Select from IN - from a newbie., visit >>>> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== >>> >> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75797.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
