---------- 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
