Re: Select from multiple tables

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