Welcome to WebmasterWorld Guest from 35.172.195.49

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql Self Join

cant even get out of the starting gate

     
3:34 pm on Sep 10, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 6, 2001
posts:2213
votes: 0


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 ....

3:45 pm on Sept 10, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 6, 2001
posts:2213
votes: 0


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?

4:43 pm on Sept 10, 2003 (gmt 0)

Senior Member from MY 

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 1, 2003
posts:4847
votes: 0


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 ;)

3:41 am on Sept 11, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 8, 2002
posts:65
votes: 0


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...
10:05 am on Sept 11, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 6, 2001
posts:2213
votes: 0


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 :)

10:58 am on Sept 11, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 6, 2001
posts:2213
votes: 0


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.

11:20 am on Sept 11, 2003 (gmt 0)

Senior Member from MY 

WebmasterWorld Senior Member vincevincevince is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 1, 2003
posts:4847
votes: 0


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?)...