Re: Select from multiple tables

This is just a stab in the dark and may be in the wrong order. If it  
does not work I apologize.

SELECT s.Title, (SELECT DISTINCT b.Board_id), (SELECT DISTINCT  
t.type), (SELECT s.School_id AND
b.School_id AND t.schoolid AS id ) FROM school s,
board_entries b, schooltypeentries t  WHERE id = 1698

HTW,

Best,
Karl


On Feb 29, 2012, at 11:46 PM, Kranthi Krishna wrote:

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

Karl DeSaulniers
Design Drumm
http://designdrumm.com


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php