hello mysql gurus, would you be able to help me with this query building? i was wondering if it's possible to use one query to produce the effects on
http://websoup.org/resources/
this page with my current table design.
CREATE TABLE dmb_resources_cats (
id smallint(6) unsigned NOT NULL auto_increment,
catname varchar(50) NOT NULL default '',
parent_id smallint(6) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY parent_id (parent_id)
) TYPE=MyISAM;
CREATE TABLE dmb_resources (
id smallint(6) NOT NULL auto_increment,
category_id smallint(6) unsigned NOT NULL default '0',
link_name varchar(50) NOT NULL default '',
link_url varchar(250) NOT NULL default '',
link_description text NOT NULL,
addedby_id smallint(6) unsigned default '0',
addedby varchar(10) NOT NULL default '',
status enum('queue','approved') NOT NULL default 'queue',
dateline int(10) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY category_id (category_id),
FULLTEXT KEY link_description (link_description)
) TYPE=MyISAM;
as you see, i'd like a query that'd order subcategories under main category (assume i only have two levels) and then its number of links in each category.
i attempted with something like
SELECT count(link.id), cat.* FROM dmb_resources_cats cat LEFT JOIN dmb_resources link ON (link.category_id = cat.id) ORDER BY cat.catname
it's saying i can't do it without group by. i don't know how to use group by. could anyone help?
also, i am willing to modify my table design if it's necessary. thanks!
