To all MySQL gurus...Left and Normal table joins..[HELP]
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";*
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
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
@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
You are welcome.Just a couple of pointers to effective DB design and SQL Querying; 1. Always design on paper - Before you get to type the query , draft on a piece of paper or notepad the tables, fields you want to use in the query and what data you want to retrieve. 2 .Try to limit the number of recordset being returned by the query. Imagine if you had 15,000 records ! Specify the fields in the query , avoid using * .... its a shortcut...but the long term repercussions will no be assuming. 3. There are alot of online resources for writing efficient SQL Queries. Take some time and go through them. www.lazydba.com , etc Kind regards, Kevin On Fri, Jul 10, 2009 at 1:09 PM, Shiro Njagi <sheeroh@gmail.com> wrote:
@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
participants (3)
-
Kevin Oeba -
Shiro Njagi -
Timothy Kyalo