mysql Self Join

cant even get out of the starting gate

[ukgimp]

If you have a categories table with a name and a parent field. It is quite easy to get the categories that are children of that cat and work out how many sub categories there are for each of the children using two queries.

Is it possible to do a self join using aliasses to get that info in one swoop.

SELECT name, id, parent
FROM categories AS A
LEFT JOIN categories AS B ON A.id = B.parent
GROUP BY B.parent

(i have just edited this 10 times as each time looks even worse)

any assistance will be appreciated and may result in a beer at Pubcon ....

[ukgimp]

I have to figure out why but this works:

SELECT DISTINCT A.name, A.id, A.parent, COUNT(B.id) FROM categories AS A LEFT JOIN categories AS B ON A.id = B.parent WHERE A.id = B.parent AND A.parent = 1 GROUP BY B.parent

Any good resources on this kind of thing?

[vincevincevince]

SELECT A.name, A.id, A.parent, COUNT(B.id) FROM categories AS A, categories AS B WHERE A.id = B.parent GROUP BY B.parent

...if it works I may hold you to that beer ;)

[Asandir]

The first part is ambiguous, I believe... when you say "SELECT name,id,parent" it doesn't know whether you mean a.name, or b.name - since both are valid. The same with id and parent. Your second query fully qualifies the fields, and works...

[ukgimp]

VVV

That works to an extent. It pulls all the ones with children. Lets say for example I have cats of

left
right
bottom
==left green
==left blue
====left blue spotty
==right green
==right orange

No the query above only pulls the ones with children. I still need the the values to show even if no children are present so that the cat still shows.

So at the moment theabove query shows

name¦ count
left¦ 2
right¦ 2
left blue ¦ 1

When what I would want it to show is the names ,left, right botton and their appropriate children cound then do the same on the fly each time a sub cat is selected.

Is this making sense :)

[ukgimp]

I think this is all down to fact that mysql does not allow subselects

if only:

SELECT name, id (SELECT COUNT(*) FROM categories WHERE parent = $x) FROM categories

Am I barking up the wrong tree.

[vincevincevince]

How about:

SELECT A.name, A.id, A.parent, COUNT(B.id) FROM categories AS A, categories AS B WHERE A.id = B.parent OR A.id = B.id GROUP BY B.parent

Although I'm not sure whether the COUNT is still accurate (maybe inflated by 1?)...