Re: [Skunkworks] To all MySQL gurus...Left and Normal table joins..[HELP]
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
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
participants (2)
-
Peter Karunyu -
Steve Obbayi