Fwd: Select from multiple tables

---------- Vidarebefordrat meddelande ----------
Från: Carl Michael Skog <[email protected]>
Datum: 1 mars 2012 17:12
Ämne: Re: [PHP-DB] Select from multiple tables
Till: Kranthi Krishna <[email protected]>


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 <[email protected]>:

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 <[email protected]> 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
> >> <[email protected]>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 <[email protected]> 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 <[email protected]>
> 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 <[email protected]>
> >>>
> >>> 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