I am a little confused > It seems to me that you are mixing two semantically different things I agree. But since both of them are attributes of school so I have to. > so you get a cartesian join over these tables(within the restrictions in the where clause) If I am not mistaken INNER JOIN is Cartesian join with where clause > Still, you want to lump them together in the same result set. You misunderstood me. I never said that I want a single row set. The following query SELECT s.Title, b.Board_id, t.type FROM (SELECT Title FROM school where School_id = 1698) s, (SELECT GROUP_CONCAT(Board_id) AS Board_id FROM board_entries WHERE School_id = 1698) b, (SELECT GROUP_CONCAT(type) AS type FROM schooltypeentries WHERE schoolid = 1698) t returns me a single row set. But I am aware that this is not practical All I want to do is to restore an object with multiple one-to-many relations Kranthi. http://goo.gl/e6t3 On 1 March 2012 21:47, Carl Michael Skog <cmskog@xxxxxxxxx> wrote: > ---------- Vidarebefordrat meddelande ---------- > Från: Carl Michael Skog <cmskog@xxxxxxxxx> > Datum: 1 mars 2012 17:12 > Ämne: Re: Select from multiple tables > Till: Kranthi Krishna <kranthi117@xxxxxxxxx> > > > It seems to me that you are mixing two semantically different > things(board_entries and schooltypeentries, both related to school). > These have no relation to each other(at least no one shown here, so you get > a cartesian join over these tables(within the restrictions in the where > clause)). > > Still, you want to lump them together in the same result set. > > The question is why ? > > Den 1 mars 2012 06:46 skrev Kranthi Krishna <kranthi117@xxxxxxxxx>: > > Hi all, >> >> SELECT DISTINCT s.Title, b.Board_id, t.type FROM school s, >> board_entries b, schooltypeentries t WHERE s.School_id = 1698 AND >> b.School_id = 1698 AND t.schoolid = 1698 >> >> this SQL query gives me >> >> Kendriya Vidyalaya 15 Kick Boxing >> Kendriya Vidyalaya 15 Karate >> Kendriya Vidyalaya 32 Kick Boxing >> Kendriya Vidyalaya 32 Karate >> >> as I stated earlier. >> >> Now using php.net/array_search php.net/foreach and >> php.net/mysql_fetch_assoc >> >> I can easily convert that into >> >> array >> name => 'Kendriya Vidyalaya' >> board_id => array >> 1 => 15 >> 2 => 32 >> type => array >> 1 => 'Kick Boxing' >> 2 => 'Karate' >> >> I am wondering if there is a better way. For example if I am able to >> get something like >> >> Kendriya Vidyalaya 15 Kick Boxing >> NULL NULL Karate >> NULL 32 NULL >> NULL NULL NULL >> >> I can use php.net/is_null instead of php.net/array_search >> >> I dont think this problem is specific to me. Please suggest some best >> practices in this case. >> >> Kranthi. >> http://goo.gl/e6t3 >> >> >> >> On 1 March 2012 10:25, Karl DeSaulniers <karl@xxxxxxxxxxxxxxx> wrote: >> > Try DISTINCT >> > >> > >> > >> > On Feb 29, 2012, at 10:28 PM, Amit Tandon wrote: >> > >> >> Dear Kranthi >> >> >> >> You have to be clear what you decide especially when you are getting >> >> multiple rows. To get just a single row you can use LIMIT clause. >> >> >> >> But it would return only one row. Now you have to decide which row. >> >> >> >> So i think you decide on what you require and see how can you uniquely >> >> identify that row >> >> ============ >> >> regds >> >> amit >> >> >> >> "The difference between fiction and reality? Fiction has to make sense." >> >> >> >> >> >> On Thu, Mar 1, 2012 at 9:45 AM, Kranthi Krishna >> >> <kranthi117@xxxxxxxxx>wrote: >> >> >> >>> Hi, >> >>> >> >>> The examples I saw were regarding cartesian join not inner join. I >> >>> will read about inner joins. Also, the example i mentioned seems to be >> >>> a mistake. Both school and type will not be similar at the same time >> >>> >> >>> >> >>> Kranthi. >> >>> http://goo.gl/e6t3 >> >>> >> >>> >> >>> >> >>> On 1 March 2012 09:26, Kranthi Krishna <kranthi117@xxxxxxxxx> wrote: >> >>>> >> >>>> Hi, >> >>>> >> >>>> Thanks for the input. I have seen some tutorials on joins, they all >> >>>> suggest that MySql returns multiple rows >> >>>> >> >>>> For example >> >>>> -------------------------- >> >>>> School | Board 1 >> >>>> -------------------------- >> >>>> School | Board 1 >> >>>> ------------------------- >> >>>> >> >>>> Now if I have another one-to-many relation >> >>>> >> >>>> ----------------------------------- >> >>>> School | Board 1 | Type 1 >> >>>> ----------------------------------- >> >>>> School | Board 1 | Type 2 >> >>>> ----------------------------------- >> >>>> School | Board 2 | Type 1 >> >>>> ----------------------------------- >> >>>> School | Board 2 | Type 2 >> >>>> ------------------------------------ >> >>>> >> >>>> Using UNIQUE or something similar (like php.net/array_search ) causes >> >>>> problems when Type 1 = Type 2 etc. >> >>>> >> >>>> Kranthi. >> >>>> http://goo.gl/e6t3 >> >>>> >> >>>> >> >>>> >> >>>> On 29 February 2012 19:43, Michael Stowe <mikegstowe@xxxxxxxxx> >> wrote: >> >>>>> >> >>>>> Select table1.item1, table2.item1 from table1 inner join table2 on >> >>> >> >>> table1.key = table2.foreignKey Where... >> >>>>> >> >>>>> >> >>>>> You can also utilize left and right join to get data if there isn't a >> >>> >> >>> direct match (ie customer may not have ordered anything so you want to >> do >> >>> a >> >>> left join on orders as there may not be any order data but you still >> want >> >>> to get the customer info). >> >>>>> >> >>>>> >> >>>>> Hope that helps, >> >>>>> Mike >> >>>>> >> >>>>> >> >>>>> >> >>>>> Sent from my iPhone >> >>>>> >> >>>>> On Feb 29, 2012, at 8:01 AM, Kranthi Krishna <kranthi117@xxxxxxxxx> >> >>> >> >>> wrote: >> >>>>> >> >>>>> >> >>>>>> Hi all, >> >>>>>> >> >>>>>> Say I have an object like >> >>>>>> >> >>>>>> array >> >>>>>> schoolName => string >> >>>>>> board => array >> >>>>>> string >> >>>>>> string >> >>>>>> >> >>>>>> I generally create two MySql tables >> >>>>>> >> >>>>>> schools: id PRIMARY KEY, SchoolName >> >>>>>> boards: id FOREGIN KEY refers Table A(id), board >> >>>>>> >> >>>>>> and then do two selects. The problem is that, the number of selects >> >>>>>> increase as the number of one-to-many relationships increase. >> >>>>>> >> >>>>>> Is there a better way to do this ? I have to extend an existing code >> >>>>>> so I cannot use any libraries like doctrine >> >>>>>> >> >>>>>> Kranthi. >> >>>>>> http://goo.gl/e6t3 >> >>>>>> >> >>>>>> -- >> >>>>>> PHP Database Mailing List (http://www.php.net/) >> >>>>>> To unsubscribe, visit: http://www.php.net/unsub.php >> >>>>>> >> >>> >> >>> -- >> >>> PHP Database Mailing List (http://www.php.net/) >> >>> To unsubscribe, visit: http://www.php.net/unsub.php >> >>> >> >>> >> > >> > Karl DeSaulniers >> > Design Drumm >> > http://designdrumm.com >> > >> > >> > >> > -- >> > PHP Database Mailing List (http://www.php.net/) >> > To unsubscribe, visit: http://www.php.net/unsub.php >> > >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > > -- > Med vänlig hälsning > Carl Michael Skog -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
