join table to itsself

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

I am using a table like follows (Just an example):
Code: [ Select ]
ID
ParentID
Name
Url
  1. ID
  2. ParentID
  3. Name
  4. Url

and this table joins on itsself to get "children" with something like follows:
Code: [ Select ]
SELECT * FROM tablename LEFT JOIN tablename AS tablename_one ON tablename.ID=tablename_one.ParentID

now the result from that is as follows (column names):
Code: [ Select ]
ID - ParentID - Name - Url - ID - ParentID - Name - Url
now as you can see the column names are duplicated because it's the same table ... the actual table I am working on has a lot of columns so I don't want to do the following:
Code: [ Select ]
SELECT tablename.ID AS tablenameID, tablename_one.ID as tablenameoneID ... etc

is there a way I can add a prefix to all of tablename_one's columns or something like that?
  • AnarchY SI
  • Web Master
  • Web Master
  • User avatar
  • Posts: 2521
  • Loc: /usr/src/MI

Post 3+ Months Ago

why not have a script generate the SQL?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

I can do that ... sounds like it could work, just wondering if there isn't an easier way ...
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6254
  • Loc: Seattle, WA

Post 3+ Months Ago

What is the information you're trying to pull from the table with the query?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

it's a left nav ... I'll show you a more relevant example ... the table looks like this:
Code: [ Select ]
Navcode INT(11) AUTO INCREMENT
Parentcode INT(11)
Url VARCHAR(255)
Title VARCHAR(255)
  1. Navcode INT(11) AUTO INCREMENT
  2. Parentcode INT(11)
  3. Url VARCHAR(255)
  4. Title VARCHAR(255)

So let's say the information looks like the following:
navigation
Code: [ Select ]
Navcode | Parentcode | Url | Title
1........0............one...One
2........1............two...Two
3........0............three.Three
  1. Navcode | Parentcode | Url | Title
  2. 1........0............one...One
  3. 2........1............two...Two
  4. 3........0............three.Three

So my menu would look as follows:
Code: [ Select ]
One
---Two
Three
  1. One
  2. ---Two
  3. Three

with two being a sub of one and the query to pull that from the database would be:
Code: [ Select ]
SELECT * FROM navigation LEFT JOIN navigation AS sub_navigation ON navigation.Navcode = sub_navigation.Parentcode WHERE Parentcode=0

This query would return the following:
Code: [ Select ]
Navcode | Parentcode | Url | Title | Navcode | Parentcode | Url | Title
1........0............one...One.....2.........1............two...Two
3........0............three.Three...NULL......NULL.........NULL..NULL
  1. Navcode | Parentcode | Url | Title | Navcode | Parentcode | Url | Title
  2. 1........0............one...One.....2.........1............two...Two
  3. 3........0............three.Three...NULL......NULL.........NULL..NULL

so you see that the column names are now duplicated, and obviously while reading through that with a php script I wouldn't be able to specify which Title column I am talking about, so I am wondering, is there a way to give all the columns from the sub_navigation table a prefix without selecting each column "as" ...

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 73 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.