Tag cloud logic [MySQL]
Posted: Wed Feb 27, 2008 8:29 am
Hello developers.
I'm stuck in writing this query for a "Tag Cloud" for my page. Basicly what i want to do is
to select the name and the 10 most "popular" "head categories" based on how many "objects"
there are in the related "sub category".
Table structure:
HC (Head category)
-------------------
hc_id
hc_name
SC (Sub category)
-------------------
hc_id <-- relates to hc_id in HC
sc_id
sc_name
OB (Objects)
-------------------
sc_id <-- relates to sc_id in SC
ob_id
ob_name
I wrote on something that i thought might be a good start... but in the end it all gets to messy for me to figure it out.
I think im lost.!
Could anyone point me in the right direction?
Thanks in advance.
I'm stuck in writing this query for a "Tag Cloud" for my page. Basicly what i want to do is
to select the name and the 10 most "popular" "head categories" based on how many "objects"
there are in the related "sub category".
Table structure:
HC (Head category)
-------------------
hc_id
hc_name
SC (Sub category)
-------------------
hc_id <-- relates to hc_id in HC
sc_id
sc_name
OB (Objects)
-------------------
sc_id <-- relates to sc_id in SC
ob_id
ob_name
I wrote on something that i thought might be a good start... but in the end it all gets to messy for me to figure it out.
Code: Select all
SELECT hc_hc.id, hc.hc_name, COUNT(ob.ob_id) AS quantity FROM hc, uc LEFT JOIN ob ON uc.uc_id = ob.uc_id WHERE
uk.hc_id = hc.hc_id ORDER BY quantity DESC LIMIT 10
I think im lost.!
Could anyone point me in the right direction?
Thanks in advance.