And don't forget to run EXPLAIN on that query to see what MySQL thinks of your query :-)


On Fri, Jul 10, 2009 at 1:49 PM, Steve Obbayi <steve@sobbayi.com> wrote:




based on what i can see, ur query is gonna miss out some data on certain siuations. change to this query:

SELECT * FROM  listing
LEFT JOIN buildings
ON buildings.buildingid=listing.listingbuildingid
LEFT JOIN categories
ON categories.categoryid=listing.listingcategory
LEFT JOIN subcategories
ON listing.listingsubcategoryid=subcategories.subcategoryid


Steve obbayi

Sobbayi on Mobile



-original message-
Subject: Re: [Skunkworks] To all MySQL gurus...Left and Normal table joins..[HELP]
From: Shiro Njagi <sheeroh@gmail.com>
Date: 10/07/2009 1:09 pm

@Kevin..
actually that was the problem!
some of the tables had no data in the fields I queried...
I solved it earlier on but thank you for the feedback!

:-)

On Fri, Jul 10, 2009 at 1:05 PM, Kevin Oeba <oeba.kevin@gmail.com> wrote:

>
> Shiro,
>
> To add to what Kyalo has requested for ( additional field names of the
> other tables) , you could also try to limit the references made to the other
> tables.
>
> I assume you are not getting any results back from the query because, well,
> the data does not match your request. Use sample data in all these five
> tables. See if the data you are  trying to match exists in those
> tables.Query could be correct, but the data is not there.
>
> Kind regards,
>
> Kevin
>
> On Fri, Jul 10, 2009 at 11:27 AM, Timothy Kyalo <this.gizmo@gmail.com>wrote:
>
>> Hi Shiro,
>>
>> Please list the field names for the other tables...
>>
>>
>> BR//
>> Kyalo
>>
>> 2009/7/10 Shiro Njagi <sheeroh@gmail.com>
>>
>>> Hello all...
>>>
>>> I have a slight problem with a certain page.
>>> I have five tables: listing, towns, buildings, categories and
>>> sub-categories.
>>>
>>> The page that i wish to display will show the listings form the listing
>>> table in the database.
>>> The listing table contains a categoryid, subcategoryid, buildingid and
>>> townid.
>>> I would like to display all these fields as names, hence the need for the
>>> join.
>>> However, a sub-category may be null, hence the need for the left join.
>>>
>>> I created the query below which is returning no data.
>>> For the life of me, I can't diagnose the problem...
>>> HELP!!
>>>
>>> *$query_listing = "SELECT * FROM buildings, categories, towns, listing
>>> LEFT JOIN subcategories ON
>>> listing.listingcategory=subcategories.subcategoryid WHERE
>>> buildings.buildingid=listing.listingbuildingid AND
>>> categories.categoryid=listing.listingcategory AND
>>> towns.townid=listing.listingtownid";*
>>>
>>> _______________________________________________
>>> Skunkworks mailing list
>>> Skunkworks@lists.my.co.ke
>>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
>>> Other services @ http://my.co.ke
>>> Other lists
>>> -------------
>>> Skunkworks announce:
>>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
>>> Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
>>> kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>>>
>>>
>>
>> _______________________________________________
>> Skunkworks mailing list
>> Skunkworks@lists.my.co.ke
>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
>> Other services @ http://my.co.ke
>> Other lists
>> -------------
>> Skunkworks announce:
>> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
>> Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
>> kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>>
>>
>
> _______________________________________________
> Skunkworks mailing list
> Skunkworks@lists.my.co.ke
> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
> Other services @ http://my.co.ke
> Other lists
> -------------
> Skunkworks announce:
> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
> Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
> kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general
>
>


_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
Other services @ http://my.co.ke
Other lists
-------------
Skunkworks announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general


_______________________________________________
Skunkworks mailing list
Skunkworks@lists.my.co.ke
http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
Other services @ http://my.co.ke
Other lists
-------------
Skunkworks announce: http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks-announce
Science - http://lists.my.co.ke/cgi-bin/mailman/listinfo/science
kazi - http://lists.my.co.ke/cgi-bin/mailman/admin/kazi/general