Page 1 of 1

groupby and joins on the same table

Posted: Wed Apr 13, 2005 5:30 am
by phpScott
a little trouble getting the sql right.
here is my table with some sample data.

Code: Select all

id  	 sub_id  	 name  	 link
1 	0 	Home 	home
2 	0 	Contact 	contact
3 	0 	Services 	services
4 	3 	DB design 	db design
5 	3 	Graphic Design 	graphic design
6 	5 	Paper 	paper
7 	5 	Electronic 	electronic
8 	2 	birmingham offices 	b'ham
9 	2 	london offices 	london
the results that I would like to get would be
sub_id is the id that the row belongs to as a sub group

Code: Select all

id  	 sub_id  	 name  	 link
1 	0 	Home 	home
2 	0 	Contact 	contact
8 	2 	birmingham offices 	b'ham
9 	2 	london offices 	london
3 	0 	Services 	services
4 	3 	DB design 	db design
5 	3 	Graphic Design 	graphic design
6 	5 	Paper 	paper
7 	5 	Electronic 	electronic
I getting to confused with the joins inner, outer, left and the various combinations.

Thanks

Jcart | Added

Code: Select all

tags[/size][/color]

Posted: Wed Apr 13, 2005 7:50 am
by Chris Corbyn
I dont understand what you're trying to do...

Looks like you tried to order by sub_id (but then you put a 0 below a 2 :? )...

Could you please try to explain a bit better. What table are you trying to join this with?

Full SQL export's of the table structures are useful for us here (phpMyAdmin can do that for you).

Cheers,

d11

Posted: Wed Apr 13, 2005 9:18 am
by phpScott
Sorry I tried to do the old copy paste thing but it didn't turn out quite right.

I have a table called menu_items in it there are 4 columns
id sub_id name link


all main menu items will have an id ie.home or services.
some of the main menu items will have sub catagories such as services might have db design and graphic design as seperate further links. so db design might have an id of 5 but its sub_id would be 2 because that is what services id is.

I'm i making this as clear as mud.

one table to create collapasable menu items for my page. actually it is to try and stuff it into a current cms that doesn't currently support the creation of collapsable menus

phpscott

Posted: Thu Apr 14, 2005 5:45 am
by phpScott
:D :D :D bump :D :D :D
do I need to explain it better or something.
any ideas :idea:

Posted: Thu Apr 14, 2005 6:00 am
by Chris Corbyn
Yeah sorry... I still dont get it..

It's the bit about joins that you're confusing me with...

What are you trying to join? You only want results from this one table right?

BTW - if you use

Code: Select all

tags to list things with whitespace it wont look messed up [/color]

Posted: Thu Apr 14, 2005 6:11 am
by n00b Saibot
why don't you do this to select all the sub-cats of a given cat id ...

Code: Select all

SELECT * FROM `category` WHERE `sub-id`= {main cat-id}

Posted: Thu Apr 14, 2005 6:44 am
by phpScott
there is only one table, trying to 'join' back on its self to get the results I would like.

supposedly you can do a join on a table back to itself as long as you do table aliasing twice. ie menu_items m, menu_itmes n.