0

I'm not sure exactly what this type of query is called, but I need to combine two SQL queries on the same table, called Page, into one, one where an SQL query will substitute one column value with another from the same table. I think this is a JOIN, but what makes it weird is I'm joining a table with itself.

The SQL table is page information, and I need it for menu hierarchy.

Here are the relevant columns in my Page table:

  • Id (a unique id of the page, primary key in the Page table, a unique integer)
  • Pagename (the name of the page, a string)
  • Submenu (the id number of the page it falls under in the menu, an integer whose values match a value in the Id column, or -1 for a top-level page)

So, for instance, we could have these values in the table.

Id               Pagename                   Submenu
0                Index Page                 -1 (top-level page)
1                Products                   0
2                Contact Us                 0
3                Chairs                     1
4                Tables                     1
5                Store Information          0

So that, if someone is on the Products page, the SQL query will return

Id               Pagename                   What Page Is This a Subpage Of
1                Products                   Index

If they're on the Chairs page, it'll return

Id               Pagename                   What Page Is This a Subpage Of
3                Chairs                     Products

This should be easy, but essentially I'm replacing that Submenu ID number with the page's name instead of its ID number.

I've been combing Google and Stack Overflow, but I'm not finding anything, maybe because I don't really know where to start in terms of what string to search for. Can anyone help me out here?

If it matters, I'm running MySQL version 5.0.11.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Steve G.
  • 411
  • 1
  • 7
  • 21
  • how many levels are there i your query you don't need chair produkt index only the "previous" btw. mysql 5.0.11 is at least 15 year out of date, that you should check with `SELECT version()` – nbk Jul 11 '21 at 23:11
  • Select .... From Page A Left Outer Join Page B on A.submenu=B.ID. A is your master record, B is your child record. – Chris Maurer Jul 12 '21 at 00:49
  • 5.0.11 is so old that it is frightening, in terms of lack of security patches – ysth Jul 12 '21 at 02:12
  • what you are asking is usually called a "self join"; searching for that will help. full on recursion is a much more complex thing, but not relevant here – ysth Jul 12 '21 at 02:13

0 Answers0