kaosweaver

I'm sorry, I didn't explain well enough what I wanted, the query you provided I needed to modify (typos) to: SELECT c_name, tblCategory.catid, jtMemberCategory.member_id, tblCategory.lft, tblCategory.rgt FROM (tblCategory left JOIN jtMemberCategory ON tblCategory.catid=jtMemberCategory.cat_id) left JOIN tblMember ON tblMember.memberid=jtMemberCategory.member_id WHERE tblCategory.cat_id=200 AND jtMemberCategory.member_id=5 ORDER BY c_name and this only returns the categories the member has, not all of the categories with a NULL in the spot for the member_id.

Here is the error: Error Number: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay SELECT c_name, catid, memberid, lft,rgt FROM (tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5) left JOIN tblMember ON member_id=memberid and memberid=5 WHERE tblCategory.cat_id=25 ORDER BY c_name I do what the error indicates (SET SQL_BIG_SELECTS=1) and the query takes almost 5 seconds. The tables are: CREATE TABLE `jtMemberCategory` ( `member_id` int(11) NOT NULL, `cat_id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `tblCategory` ( `catid` int(11) NOT NULL auto_increment, `c_name` varchar(250) character set utf8 collate utf8_unicode_ci NOT NULL, `c_namefarm` varchar(250) NOT NULL, `cat_id` int(11) NOT NULL default '-1', `c_desc` text character set latin1 collate latin1_general_ci NOT NULL, `lft` int(11) NOT NULL, `rgt` int(11) NOT NULL, `rank` int(11) NOT NULL default '10', PRIMARY KEY (`catid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `tblMember` ( `memberid` int(11) NOT NULL auto_increment, `m_companyname` varchar(250) NOT NULL, `m_contact_name` varchar(50) NOT NULL, `m_contact_email` varchar(50) NOT NULL, `m_address` varchar(100) NOT NULL, `m_address2` varchar(100) default NULL, `m_city` varchar(50) NOT NULL, `m_state` varchar(2) NOT NULL, `m_zip` varchar(10) NOT NULL, `m_phone` varchar(20) NOT NULL, `m_email` varchar(50) NOT NULL, `m_url` varchar(50) NOT NULL, `m_password` varchar(10) NOT NULL, `m_publicRestroom` varchar(1) NOT NULL default 'N', `m_handicapp` varchar(1) NOT NULL default 'N', `m_handicappRestroom` varchar(1) NOT NULL default 'N', `m_handWashing` varchar(1) NOT NULL default 'N', `m_internship` varchar(1) NOT NULL default 'N', `m_educationTour` varchar(1) NOT NULL default 'N', `m_groupTour` varchar(1) NOT NULL default 'N', `m_motorCoaches` varchar(1) NOT NULL default 'N', `m_summary` text, `m_longSummary` text, `m_photo` varchar(100) default NULL, `m_featured` varchar(1) NOT NULL default 'N', `m_cellphone` varchar(1) NOT NULL default 'N', `m_csamemberships` int(11) NOT NULL default '0', `m_csaweeks` int(11) NOT NULL default '0', `m_foodseating` varchar(1) NOT NULL default 'N', `m_summaryholding` text, `m_longsummaryholding` text, `m_photoholding` varchar(250) default NULL, `m_approved` varchar(1) NOT NULL default 'N', `m_startdate` date NOT NULL, `sub_id` int(11) NOT NULL, PRIMARY KEY (`memberid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The desired result is a complete list of categories under a parent category with the member_id either set to the member's ID or NULL (so I can display a checkbox list with the categories the member has preselected) The data should look like: c_name catid memberid lft rgt Bathroom Accommodations49353758 Bed & Breakfast479NULL 910 Bunkhouse 481NULL1314 Cabins482NULL1516 Conestoga Wagons 740NULL8788 `tblCategory` has ~1000 entries `tblMember` has ~3500 entries `jtMemberCategory` has ~245,000 entries The SQL I am using: SELECT c_name, catid, memberid, lft,rgt FROM (tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5) left JOIN tblMember ON member_id=memberid and memberid=5 WHERE tblCategory.cat_id=25 ORDER BY c_name Some of the stuff I've tried - removing the second left join: SELECT c_name, catid, member_id, lft,rgt FROM tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5 WHERE tblCategory.cat_id=25 ORDER BY c_name Same time result. (and the same data returned, so I'm made it a little less complex) I added DISTINCT (which wasn't an issue with this version of SQL, but was with some of the versions I've tried) The member_id in the column list isn't a field that has to contain meaningful information, just a NULL and something else to indicate that the left join was a match. I could do this in PHP with a loop and just pull the categories out and match later on (maybe 50 sub categories per parent, so we're talking 50 loops x the number of matches in the subcategory for the member, not very efficient, imo) mysql version - 5.0.45-community-log I don't know how to make my SQL more efficient, any assistance would be appreciated. Thank you

I'm hoping that is what I want... mySQL v4.1 Here is the parent query SELECT business.businessid, business.name, city, logo FROM business, xbusinesscategorytype WHERE business.businessID = xbusinesscategorytype.businessid ORDER BY city Each business has categories and I want to have each category the business is part of listed as well, we get the categories by: select categorytype.name from xbusinesscategorytype, categorytype where categorytype.categoryTypeID=xbusinesscategorytype.categoryTypeID AND businessID=1032 What I thought would work would be to do this: SELECT business.businessid, business.name, city, CONCAT_WS(",",select categorytype.name from xbusinesscategorytype, categorytype where categorytype.categoryTypeID=xbusinesscategorytype.categoryTypeID AND businessID=business.businessid) as listingDesc, logo FROM business, xbusinesscategorytype WHERE business.businessID = xbusinesscategorytype.businessid ORDER BY city An example of the end result would be: 1000 | Sears | Tampa | lawn care, appliances, electronics, clothes 1001 | The Gap | Tampa | clothes 1002 | Spensers | St Petersburg | gifts, posters, collectibles and so on. I know I can't be the only/first person with this issue - but I just don't know how to do the right search to find the right solution (some were close, but they dealt with the subquery outside of a column reference, couldn't handle that the subquery returned more than one record, dealt with group by aggregate functions, etc). I would appreciate some help with this, thanks! K