Mysql nested query help!

[kitrak2004]

I need help in following query.

I am creating a database for Library listing for my college.

There are categories , sub - categories, sub sub - categories and so on.

What i want is what is similar at hotscripts.com . In PHP category, it shows number of listings inside it. Similarly, i wanted a mysql query which would directlt provide me number of listings inside it.

I have 3 tables

_______________________
One : stores the book categories
catid -> every book category has an unique ID
superset -> stores the parent category

For Ex.: If Entertainment category has catid = 10
then Sports category which belongs to Entertainment category has superset = 10


`category` (
`catid` bigint(6) NOT NULL default '0',
`name` varchar(60) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`superset` bigint(6) NOT NULL default '0',
`addable` tinyint(1) NOT NULL default '1',
UNIQUE KEY `catid` (`catid`)
) TYPE=MyISAM COMMENT='listing of all categories ';

_______________________
Two : Links the listings to the category they belong to.

`list_cat` (
`listid` bigint(6) NOT NULL default '0',
`catid` bigint(6) NOT NULL default '0'
) TYPE=MyISAM COMMENT='shows which listing is in which category';

_______________________
Three : Listing description.
`listing` (
`title` varchar(50) NOT NULL default '',
`listid` bigint(6) NOT NULL default '0',
PRIMARY KEY (`listid`)
) TYPE=MyISAM COMMENT='listing details';




I can do that using PHP loops , but it takes up lot of processor time and makes the local server slow. I guess this can be made easier of the server using some MYSQL command itself.

If someone could help, it would be very greatfull.

[Belisarius]

I'm not sure I followed exactly what you wanted to do, but let's see if I understand . . .

I know in PostgreSQL, you have the "count" function, that'll give you a count of the number of results. I assume MySQL has a similar function.

So just create a query for the for a category (alter this to taste, it's a pseudo-query), "SELECT count(l.*) FROM listing l, list_cat lc, category c WHERE l.listid=lc.listid AND lc.catid=c.catid AND c.name=<variable>;", where <variable> is insterted into the query in PHP. That should return you the number of the listings in a given category name.

[kitrak2004]

no, what i want is if suppose there is an Entertainment category

Sports : 10
Television : 20
Movies : 15

So in first page when user has not gone into Entertainment category, I'm required to show a link with number of listings inside Entertainment category.

For above example , link would be " Entertainment (45) " , similar to what you see at www.hotscripts.com

PHP(10,902)

Hope, i made myself clear. Please help me out!

Kitrak 2004

[Belisarius]

I'm not sure it's possible to do that in MySQL. It sounds like you need some sub-selects, which if memory serves me don't exist in MySQL. Maybe in the beta versions it does. I know PostgreSQL supports them.

Basically, you'd need to query using the count function inside a query that returns all the categories.

[sde]

probably not the answer you want to hear, but how about adding a 'count' field to your category table. you would need to create the logic to increment and decrement the count of all higher level categories when you add or remove a listing.

`category` (
`catid` bigint(6) NOT NULL default '0',
`count` int(10) NOT NULL default '0',
`name` varchar(60) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`superset` bigint(6) NOT NULL default '0',
`addable` tinyint(1) NOT NULL default '1',
UNIQUE KEY `catid` (`catid`)
) TYPE=MyISAM COMMENT='listing of all categories ';