groupby and joins on the same table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

groupby and joins on the same table

Post 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]
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

:D :D :D bump :D :D :D
do I need to explain it better or something.
any ideas :idea:
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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]
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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}
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

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